MySQL - Multiple values in a single row

Question

I have the following tables :

TABLE Users :

id_user | name | job
--------|------|----
1       | John | Director
2       | Lila | Assistant
3       | Paul | Director
4       | Jude | Assistant

TABLE Site :

id_site | city 
--------|-------
110     | Paris 
111     | London 

TABLE Contact :

id_user | id_site | is_assistant | is_director
--------|---------|--------------|------------
1       | 110     | NULL         | 1
2       | 110     | 1            | NULL
3       | 111     | NULL         | 1
4       | 111     | 1            | NULL

Let me explain. I have the different users of the company in the table Users ; the different locations of the company in the table Site and finally a table Contact that relates the table users with the table Sites.

Here is my query :

SELECT 
site.*,
`assistant`.`job` AS "Assistant Job",`assistant`.`name` AS "Assistant name", 
`directors`.`job` AS "Job director",`directors`.`name` AS "Director name"
FROM `site`
LEFT OUTER JOIN contact ON contact.id_site = site.id_site                   
LEFT OUTER JOIN `users` AS assistant ON `contact`.`id_user` = `assistant`.`id_user` AND `contact`.`is_assistant` = "1"
LEFT OUTER JOIN `users` AS directors ON `contact`.`id_user` = `directors`.`id_user` AND `contact`.`is_director` = "1"
ORDER BY site.id_site

The query is quite OK, but the problem is that I have results like :

id_site | city   | Assistant Job | Assistant name | Job director | Director name
--------|--------|---------------|----------------|--------------|-------------- 
110     | Paris  | NULL          | NULL           | Director     | John
110     | Paris  | Assistant     | Lila           | NULL         | NULL
111     | London | NULL          | NULL           | Director     | Paul
111     | London | Assistant     | Jude           | NULL         | NULL

I'd like to have all the information on the same row instead of having 2 almost identical rows. I'd like something like :

id_site | city   | Assistant Job | Assistant name | Job director | Director name
--------|--------|---------------|----------------|--------------|-------------- 
110     | Paris  | Assistant     | Lila           | Director     | John
111     | London | Assistant     | Jude           | Director     | Paul

I hope I'm being clear enough. Could you help me please ?

Thank you very much.

Stefey

Answer 1

If you put the two contact IDs on the same line, it should work. The issue is that because it's joining to two rows, and joining to the split rows. So if you join the contact table twice (with a second clause for the is_director and is_assistant), you should only have 1 result for each. Then join to your respective users tables.

SELECT 
    site.*,
    `assistant`.`job` AS "Assistant Job",
    `assistant`.`name` AS "Assistant name", 
    `directors`.`job` AS "Job director",
    `directors`.`name` AS "Director name"
FROM 
    `site`
LEFT OUTER JOIN 
    `contact` AS ContactAssistant
    ON ContactAssistant.id_site = site.id_site 
    AND ContactAssistant.is_assistant = "1"   -- Add this clause here
LEFT OUTER JOIN 
    `contact` AS ContactDirector
    ON ContactDirector.id_site = site.id_site 
    AND ContactDirector.is_director = "1"     -- Add this clause here             
LEFT OUTER JOIN 
    `users` AS assistant 
    ON ContactAssistant.`id_user` = `assistant`.`id_user` 
    -- AND `contact`.`is_assistant` = "1" -- No longer needed
LEFT OUTER JOIN 
     `users` AS directors 
     ON ContactDirector.`id_user` = `directors`.`id_user` 
     -- AND `contact`.`is_director` = "1" -- No longer needed
ORDER BY site.id_site

Unrelated, are your is_assistant and is_director fields (TINY)INT or VARCHAR? I'm assuming they're numeric, so you may want to make sure you're treating them as such.