Thread: LEFT JOIN question...
10-18-2006, 01:41 PM #1Web Hosting Evangelist
- Join Date
- Aug 2005
- EIB Network
LEFT JOIN question...
I have a fairly simple question... I have a 'users' table that holds member info and a 'usergroup' table that assigns a specific usergroup(s) (like advertiser, best avatar, etc) with a specific user. For instance, most users do not have a record in the 'usergroup' table, but those who are members of a usergroup, have a record in the'usergroup' table. Sometimes they have more than one, if they are a member of more than one usergroup.
I have a forum-like application that outputs posts made by users and I want to also retrieve each users usergroup information (if they have some) with this query. The problem I am running into is that it is only returning one usergroup record if more than one usergroup record is present for a user. So if a user has say 5 usergroup records in the 'usergroup' table, I would like to have all 5 records within the data set so I can use it in my application. By using the LEFT JOINS below, it is only returning one of the records.
Is there a way to return all the records in the 'usergroup' table that belong to the specific user the while loop is on (mysql_fetch) in some sort of array or something? Thanks for your help!
SELECT `u`.*, `ug`.*, `p`.* FROM `users` AS `u` LEFT JOIN `usergroup` AS `ug` ON (`u`.`usergroupid`=`ug`.`usergroupid`) LEFT JOIN `posts` AS `p` ON (`u`.`userid`=`p`.`userid`);
People train run out of Stubville.
10-18-2006, 03:15 PM #2Junior Guru Wannabe
Originally Posted by seodevhead
- Join Date
- Mar 2003
Try to split it into "get-user-groups" query and "get-user-posts".
SELECT u.*, ug.* FROM users u LEFT JOIN usergroup ug ON (u.usergroupid=ug.usergroupid) ....;
Now get all the posts:
SELECT p.* FROM posts p WHERE p.userid = %d ....
Do not forget to create an index on `posts.userid` column!Search tutorial collection Look4docs.com (Linux, PHP, MySQL, Java and more)
10-18-2006, 07:41 PM #3Junior Guru Wannabe
- Join Date
- Oct 2006
One problem might be that you don't have a single index that exists is all three tables. In order for the database system to join the usergroup and posts tables there has to be a column that you can join on. Unless you want the cartesian product, and I'm certain you don't want that in this case.
I'm a little confused about how you've set up your database. You say you have a one-to-many relation between users and usergroups (one user -> many possible usergroups). So why are you using a usergroupid in the user table? Everything I've read, as well as my own limited experience setting up databases would lead me to set this up with a 'userid' in the usergroup table. Explain how you've set up your tables and it would be easier to answer your question.