PHP SQL, SELECT corresponding data from three tables at the same time?

Question

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

'u' has

• User ID
  
• Divid

'd' has

• Divid
  
• divname
  

's' has

• SNAME
  
• primaryuserid
  
• secondaryuserid

Now what I would like to do is display a table with rows in the following format

• userid, divname, sname

Plus, specify a way to decrypt whether the userid is primary or secondary for this shortcut table.

I can show userid and divname using left join, but I don't know how to add a third table? To make this more difficult, there can be more than one call for each user, up to ~ 20. Is there a way to display 0-20 messages depending on the user ID, separated by commas?

, , u d , .

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

Answer 1

. , " 0-20 , ", MySQL GROUP_CONCAT(), ( ):

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

, , , . , , , :

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

( , ) , .

Answer 2

s :

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

20 , , , PHP. , . MySQL 20 , . :

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

Answer 3

Create an Inner Join and put it in the view. Then we can just make a choice in the view.