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`);

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`);

This query looks too havy to SQL server.
Try to split it into "get-user-groups" query and "get-user-posts".

Somthing like:

SELECT u.*, ug.* FROM users u LEFT JOIN usergroup ug ON (u.usergroupid=ug.usergroupid) ....;This will select all the user information with one usergroup per row.

Now get all the posts:
SELECT p.* FROM posts p WHERE p.userid = %d ....

Plain select will get all user posts for known user_id much faster than select with "Left Join".
Do not forget to create an index on `posts.userid` column! :)

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.

Andrew