CODEWITHSUNDEEP
mysqlsqljoingroup-concat
Sam Flynn
Sam Flynn

Reputation: 27

Why does combining multiple rows into one column duplicates data?

I know this question had been answered before but I couldn't find one with a similar problem.

I created an example table below:

TableA - aID | aName
          1  | House


TableB - bID | bmodel    | bname  | aID
          1  | studio    | classy |  1
          2  | studio    | small  |  1
          3  | apartment | classy |  1

TableC - cID | cmodel   | cname  | aID
          1  | bedroom  | big    | 1
          2  | bedroom  | small  | 1
          3  | bedroom  | big    | 1

I'm trying to achive

aID | aName |          bmodel bname                            |     cmodel cname
 1  | house | studio classy, studio small, apartment classy    | bed big, bed small, bed big

Here's what happens

aID | aName |          bmodel bname                                                                          |     cmodel cname
 1  | house | studio classy, studio small, apartment classy, studio classy, studio small, appartment classy  | bedroom big, bedroom small, bedroom small, bedroom big, bedroom small, bedroom small

It just repeats even more the more GROUP_CONCAT I add.

The code I used:

SELECT 
    TableA.aId, TableA.aname,
    GROUP_CONCAT(CONCAT(TableB.bmodel, ' ', TableB.bname) SEPARATOR ', ') as BModNam,
    GROUP_CONCAT(CONCAT(TableC.cmodel, ' ', TableC.cname) SEPARATOR ', ') AS CModNam

FROM TableB
    JOIN cases
        ON TableB.aID = TableA.aId
    JOIN TableC
        ON TableC.aid = TableA.aID

WHERE
    TableA.aID=1

I know I could use distinct but would exclude rows that had duplicates (i.e. studio, classy, bedroom, big)

Upvotes: 0

Views: 53

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

Make the calls to GROUP_CONCAT as subqueries in the select statement:

SELECT
    aID,
    aName,
    (SELECT GROUP_CONCAT(CONCAT(b.bmodel, ' ', b.bname))
     FROM TableB b WHERE b.aID = t.aID GROUP BY b.aID) AS bmodel_bname,
    (SELECT GROUP_CONCAT(CONCAT(c.cmodel, ' ', c.cname))
     FROM TableC c WHERE c.aID = t.aID GROUP BY c.aID) AS cmodel_cname
FROM TableA t
WHERE aID = 1;

The problem with joining, and with your current output, is that you are multiplying together the sets of models and names from the two tables. Using subqueries gets around this, because each subquery generates a focused CSV list for only that table.

Output:

aID aName   bmodel_bname                  cmodel_cname
1   House   studio classy,studio small,   bedroom big,bedroom small,
            apartment classy              bathroom big

Demo

Upvotes: 1

Related Questions