Mysql query problem

[link7722]

I have the following tables:

Users

user_id ------- display_name ------- 

Groups

group_id --------- group_name ------- admin_id ------ superadmin_id

I want to populate an html table like this:

group_name -------- display_name_of_group_admin --------- display_name_of_group_superadmin  

My problem is that i can't get these results with one query in order to loop through the results.

I can do :

SELECT groups.group_name, users.display_name FROM group INNER JOIN users ON groups.admin_id = users.user_id


and


SELECT users.display_name FROM group INNER JOIN users ON groups.superadmin_id = users.user_id

 

but that doesn't allow me to populate the table the way I want.

[mac_gyver]

the reason you cannot write the query to do what you want, is because your data isn't normalized. by having two separate columns, holding same meaning data (a user id), that only differs in its assigned role - admin or superadmin, you cannot write a simple query that does what you want. also, what if you need more than one admin for a group?

 

you need a separate table to hold this related data (user_id and type - admin/superadmin). then you would simply join that table in your query and get the user id and type for each admin/superadmin for each group.

[Barand]

Alternatively, you could connect twice to the user table (effectively treating on physical table as two logical tables). Use different table aliases and also different column aliases for the display name fields

SELECT g.group_name, u1.display_name as admin, u2.display_name as super 
FROM group g
INNER JOIN users u1 ON g.admin_id = u1.user_id
INNER JOIN users u2 ON g.superadmin_id = u2.user_id

Edited October 4, 2013 by Barand