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

looks like you need to PIVOT the results.. this question is asked before stackoverflow.com/search?q=mysql+pivot — Raymond Nijland
– Raymond Nijland, Commented Nov 2, 2017 at 15:35
Given that you have access to PHP, I think you're far better off resolving issues of data display there rather than in the SQL query, as was suggested by the answers to the 'duplicate question'. That said, there are lots of examples of this approach out there too. — Strawberry
– Strawberry, Commented Nov 2, 2017 at 16:18

kchason · Accepted Answer · 2017-11-02 16:42:46Z

2

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.

answered Nov 2, 2017 at 16:42

kchason

2,88522 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stefey Over a year ago

Thank you very much !!! I just adapted it to my query and that's working perfectly. is_assistant and is_director are BOOLEAN ! Thanks again.

kchason Over a year ago

Awesome! If it solved your problem, feel free to accept my answer :)

Collectives™ on Stack Overflow

MySQL - Multiple values in a single row

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related