trying to return multiple rows from one table for each row in another

Vigilante

I'm certainly not averse to taking the initiative to learn myself but the explanations one finds at mysql.com are... somewhat baffling to me and finding a specific answer like this could take years so I hope someone will help me with this.

What I'm trying to do has several applications. I recently decided my user registration system ought to allow one user to be a member of multiple usergroups so that if I could restrict access to some sections of the site to only members of usergroup x while still having that user in some other group.

So here is my current working query to get user data which supports only one usergroup

SELECT users., usergroups., useroptions.* FROM users,usergroups,useroptions WHERE users.userid='$user' AND usergroups.usergroupid=users.usergroup AND useroptions.userid=users.userid

It seems to me that it's possible for sql to also get all usergroups that auser is a member of in this same query, so I have created the following table

CREATE TABLE usersingroup (
userid int(10) unsigned not null default '0',
usergroupid int(10) unsigned not null default '0')

This is what I need isn't it? The reason I posted this in the php forum is because I am assuming I not only need some kind of special JOIN to get the appropriate data I also need to iterate some resulting array and I'm not quite sure how to go about it.

Whatever the solution to this problem is, I will also apply it to my forum system so it can support unlimited moderators in much the same fashion, so any assistance would be doubly helpful. I've only realized recently how thin my understanding of sql is but now that I've learned things like this are possible in a single query I'm determined to make things as efficient as possible.

eoghain

So what you want is everything from your users table, everything from your useroptions table, and every thing from the usergroups table, but you want to match more than 1 row from the usergroups table, is this correct?

Here is what I see of your data:

Users:
--------
user_id int not null
name varchar

UserOptions:
----------------
userOption_id int not null
user_id int not null
option1 bool
option2 bool

UserGroups:
----------------
userGroup_id int not null
user_id int not null
name varchar

The data you have in these tables is a single user entry, a single useroption entry for your user, and 2 usergroup entries for your user:

Users:
--------
user_id - 1
name - john doe

UserOptions:
-----------------
useroption_id - 1
user_id - 1
option1 - true
option2 - false

UserGroups:
----------------
usergroup_id - 1
user_id - 1
name - group 1

usergroup_id - 2
user_id - 1
name - group 2

And what you want to get back is all of that data in a single query. If this assumtion is correct what you want to do is run a query like this:

SELECT Users.Name as user_name,
       UserOptions.option1,
       UserOptions.option2,
       UserGroups.name as group_name
  FROM Users
 INNER JOIN UserOptions ON (Users.user_id = UserOptions.user_id)
  LEFT OUTER JOIN UserGroups ON (Users.user_id = UserGroups.user_id)
 WHERE Users.user_id =1;

Now that above query will give you every piece of data that exists in the first 2 tables matched with every piece of data that exists in the 3rd table (UserGroups) So what you would get back for the data listed is 2 rows:

user_name, option1, option2, group_name
-------------------------------------------------------
john doe, true, false, group 1
john doe, true, false, group 2

Now that you have that data all you need is the PHP to convert the multiple rows of mostly duplicate data into something that your program will understand.

Vigilante

I've applied the technique and simplified the usergroup system so it is no longer necessary to get every row from the usergroups table, I just load their ids from the linking table of userids and usergroupids but isn't there a significant amount of overhead in getting the users row several times for every group the user is a member of?

eoghain

Well there are several ways to achieve your goals, and they all involve overhead.

The way I showed you get alot of duplicate data that you don't really need. You will also have to perform some looping logic and your loop will be larger than needed.
Query your groups table for every user you return. The overheard you get here is running an extra set of queries 1 for every user.
Build a list of userid's and run a single query against groups for all of these ids. For this you will be running a second query, and doing a nested loop structure (loop over users and in that loop, loop over groups).

There is no perfect answer for this type of situation that I've ever found.