PHP SQL, SELECT corresponding data from 3 tables at once?

Question

I have 3 tables, 'u' 'd' 's'

'u' has

• userid
  
• divid

'd' has

• divid
  
• divname
  

's' has

• sname
  
• primaryuserid
  
• secondaryuserid

Now what I'd like to do is display a table with rows of the following format

• userid, divname, sname

Plus figure out a way to decipher whether userid is a primary or secondary for this sname table.

I'm able to show userid and divname using a left join, but I don't know how I would add a third table? To make it trickier, there can be more than 1 snames for each userid, up to ~20. Is there a way to display 0-20 snames depending on the userid, separated with commas?

What I have currently is just and u and d tables corresponding to each other.

SELECT
e.userid,
e.divid,
d.divname
FROM
e
LEFT JOIN d ON (e.divid = d.id)
ORDER BY e.userid

Answer 1

You can join the tables, and s twice for the two possible relations:

select 
    u.userid
,   d.divname
,   coalesce(s1.sname, s2.sname)
,   case when s1.sname is not null then 'Primary'
         when s2.sname is not null then 'Secondary'
         else 'None' end
from u
join d on d.divid = u.divid
left join s s1 on s1.primaryuserid = u.userid
left join s s2 on s2.secondaryuserid = u.userid

Displaying up to 20 usernames separated by comma's is best done on the client side, in PHP. It is possible to do it in the database, but the method depends on your DBMS. For MySQL, limiting a subquery to 20 rows is hard, but creating comma-separated lists is easy. For example:

select
    group_concat(coalesce(s1.sname,s2.sname) separator ',')
,   ...
from u
...
group by u.userid

Answer 2

You're looking for a couple of different things here. First of all, the "way to display 0-20 snames depending on the userid, separated with commas" can be done with MySQL's GROUP_CONCAT() function, so your query would look like this (no distinction between primary and secondary yet):

SELECT u.userid,
    d.divname,
    GROUP_CONCAT(s.sname SEPARATOR ', ') AS "snames"
FROM u
    LEFT JOIN d ON d.divid = u.divid
    LEFT JOIN s ON (s.primaryuserid = u.userid OR s.secondaryuserid = u.userid);

Now, to be able to distinguish whether the user is primary or secondary, you have to get a little fancier. I'd probably do it with a union, like this:

SELECT u.userid,
    d.divname,
    GROUP_CONCAT(s.sname SEPARATOR ', ') AS "snames",
    'Primary' AS "category"
FROM u
    LEFT JOIN d ON d.divid = u.divid
    LEFT JOIN s ON s.primaryuserid = u.userid
UNION ALL
SELECT u.userid,
    d.divname,
    GROUP_CONCAT(s.sname SEPARATOR ', ') AS "snames",
    'Secondary' AS "category"
FROM u
    LEFT JOIN d ON d.divid = u.divid
    LEFT JOIN s ON s.secondaryuserid = u.userid

This will give you two rows (one Primary, one Secondary) for each userid with comma-separated lists of the snames.

Answer 3

Create a Inner Join and put it in a view. Then we can just do a select on the view.