Multiple joins left double value-CodePudding

I have below query but I have problem because if I have two value in column Service and in column Notes one value that result below query give double value in note_names

SELECT 
    p.id, p.name, p.email, 
    GROUP_CONCAT(s.service_name SEPARATOR ',') AS services_names,
    GROUP_CONCAT(n.note_name SEPARATOR ',') AS note_names 
FROM
    persons p 
LEFT JOIN 
    services s ON p.id = s.id_person 
LEFT JOIN 
    notes n ON p.id = n.id_person 
WHERE 
    p.id = 179

CodePudding user response：

You are getting a Cartesian result/product in your query. Whenever you do a join from one table to another, and there are multiple entries in any given table, you are touching that first with EACH in the other. In your scenario, to keep a 1:1 ratio, you probably need to pre query your services table to get its aggregated parts and similarly with notes grouped by the person, THEN join back to the person table like.

SELECT 
      p.id,
      p.name, 
      p.email, 
      s.services_names,
      n.note_names
   FROM
      persons p 
         LEFT JOIN 
         ( select 
                 id_person,
                 GROUP_CONCAT(service_name SEPARATOR ',') AS services_names
              from
                 services
              WHERE 
                 id_person = 179
              group by
                 id_person ) s
            ON p.id = s.id_person 
         LEFT JOIN 
         ( select 
                 id_person,
                 GROUP_CONCAT(note_name SEPARATOR ',') AS note_names
              from
                 notes
              WHERE 
                 id_person = 179
              group by
                 id_person ) n
            ON p.id = n.id_person 
   WHERE 
      p.id = 179

To keep the inner pre-queries from querying EVERY person's services or notes, I did keep the WHERE clause in there as well. Just in case you wanted to do several people, you are still not querying EVERY one.

CodePudding user response：

If you are looking to get Distinct values Just use:

SELECT 
    p.id, p.name, p.email, 
    GROUP_CONCAT(s.service_name SEPARATOR ',') AS services_names,
    GROUP_CONCAT(DISTINCT(n.note_name) SEPARATOR ',') AS note_names 
FROM
    persons p 
LEFT JOIN 
    services s ON p.id = s.id_person 
LEFT JOIN 
    notes n ON p.id = n.id_person 
WHERE 
    p.id = 179

Else yes, as you can see in @DRapp's answer.