GROUP_CONCAT on two columns

Question

I have those tables:

Party

id | date | place

Party_Attendands

id | party_id | person_id | unknow_person_id

Persons

id | name | lastname

I want to denormalize some data and avoid the use of Party_Attendands. Basically I want a view like

Party & PartyPeople

id | date       | place | attendands_list
0  | 01/01/2016 | NY    | 1,2,3,4

The query below returns the person_ids, but not the unknow_person_ids. People who crashed the party are person too :). If it's not clear, unknow_person_ids are just ids which are not present in Persons. So each row of Party_Attendands contains a person_id or unknow_person_id but not both.

So attendands_list should contains person_ids and unknow_person_ids, currently handles just the former.

SELECT party.party_id, party.date, attendands.list
FROM party
LEFT OUTER JOIN
(
  SELECT party_id , GROUP_CONCAT( Party_Attendands.person_id
  ORDER BY person_id
  SEPARATOR  ',' ) AS list
  FROM Party_Attendands
  GROUP BY Party_Attendands.party_id 
) AS attendands 
ON party.party_id  = attendands.party_id

Are your `person_id` values and your `unknown_person_id` values taken from the same sequence of numbers? Can a Person have the same id value as a different Unknown person? — O. Jones, Dec 12 '16 at 23:52
No, there is no way to have ambiguous data. (`unknown_person_id` starts from 1000, `person_id` ends about 300 ) — alfredopacino, Dec 12 '16 at 23:59
http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 — e4c5, Dec 13 '16 at 00:56
There is no question if you don't read the entire post. I would like to know the reason of the downvotes. — alfredopacino, Dec 13 '16 at 09:24
I didn't downvote, but I suspect people downvoted because many question posts start with the question and then give the background. They way you structured your question is called *burying the lede* in English-language journalism: we have to study the post to find the key point. — O. Jones, Dec 13 '16 at 12:12

score 0 · Accepted Answer · answered Dec 13 '16 at 00:02

TL;DR Use UNION.

You can take the union of person_id and unknown_person_id values, like so.

                SELECT party_id, person_id
                  FROM Party_Attendands
                 UNION          
                SELECT party_id, unknown_person_id AS person_id
                  FROM Party_Attendands

Then you can group these items, like so:

   SELECT party_id, 
          GROUP_CONCAT(DISTINCT person_id ORDER BY person_id) persons
     FROM (
                SELECT party_id, person_id
                  FROM Party_Attendands
                 UNION          
                SELECT party_id, unknown_person_id AS person_id
                  FROM Party_Attendands
          ) a
    GROUP BY party_id

Then you can join that result to your other table.

SELECT p.id party_id, p.date, p.place, q.persons
  FROM Party p
  LEFT JOIN (
           SELECT party_id, 
                  GROUP_CONCAT(DISTINCT person_id ORDER BY person_id) persons
             FROM (
                        SELECT party_id, person_id
                          FROM Party_Attendands
                         UNION          
                        SELECT party_id, unknown_person_id
                          FROM Party_Attendands
                  ) a
            GROUP BY party_id
       ) q ON p.id = q.party_id

I suppose it's not a party if nobody shows up. But still, LEFT JOIN will allow your result set to show rows from your Party table even if no corresponding rows appear in your other table.

Some are failed party :) so I used `left outer join`. I tried yours and I'm not sure why it still handles the failed party with a plain `left join`. Anyway I made it works with 2 rough `left outer join`, yours is better and I will use it. — alfredopacino, Dec 13 '16 at 00:29

GROUP_CONCAT on two columns

1 Answers1