Page 1 of 1

Select query using referential tables...

Posted: Tue Jul 05, 2005 10:28 am
by quince
i have a user table and then a user_groups table that references the user table I want to get the list of users that are part of a specific group. I can query the group and get the user_id that is reference, but then I want it to give me that user name that goes whit it. Does anyone know how to write this type of query or do I need to make 2 queries? where the 1st query gets the user_id's from the user_gruops table and the second takes the user_id's and get the user_names from the users table.

Thanks for the help!
Quince

Posted: Tue Jul 05, 2005 10:33 am
by Burrito
you just need to "join" the two tables. You could do it with an Inner join, or something like this:

Code: Select all

select u.username, g.groupname from users u, groups g where u.id = g.userid

Posted: Tue Jul 05, 2005 10:35 am
by quince
thanks I'll try that. :P

Posted: Tue Jul 05, 2005 10:45 am
by quince
I guess I'm still a little confused...

here is what I tried to do:

Code: Select all

$result = mysql_query(&quote;SELECT u.username, g.user_id FROM user u, user_group g WHERE user_group=&quote;group1&quote;)
but where do I relate the user_id?

Posted: Tue Jul 05, 2005 10:50 am
by Burrito

Code: Select all

$result = mysql_query(&quote;SELECT u.username, g.user_id FROM user u, user_group g WHERE g.user_group='group1' and u.id = g.user_id&quote;)

Posted: Tue Jul 05, 2005 11:13 am
by quince
Thanks! U DA MAN! :lol: