CODEWITHSUNDEEP
mysqldatabasegroup-concat
SecondGear
SecondGear

Reputation: 1113

mysql: return multiple csv columns from joins

I have this query:

SELECT * 
FROM  `test` t
LEFT JOIN  `result` r ON r.test_id = t.id
LEFT JOIN  `parameter` p ON p.test_id = t.id

which returns:

id                name   id test_id      description    id test_id          name
 1 test w/o parameters    1       1 the first result  NULL    NULL          NULL
 1 test w/o parameters    2       1 the second result NULL    NULL          NULL
 2 test w/ paramters   NULL    NULL              NULL    1       2  command_line
 2 test w/ paramters   NULL    NULL              NULL    2       2        userid

what I want returned is:

id                name                             results           parameters
 1 test w/o parameters  the first result,the second result                 NULL
 2 test w/ paramters                                  NULL  command_line,userid

I've tried to get GROUP_CONCAT to work but the NULLS are discarded. Is there some way to convince GROUP_CONCAT to return nulls?

What I'd really like to have is something like this but this is all wrong:

SELECT * , group_concat(r.description), group_concat(p.name)
  FROM `test` t 
  left join `result` r on r.test_id = t.id
  left join `parameter` p on p.test_id = t.id

This returns the non-NULL rows:

SELECT * , group_concat(r.description)
  FROM `test` t 
  LEFT JOIN `result` r on r.test_id = t.id
  LEFT JOIN `parameter` p on p.test_id = t.id

Upvotes: 1

Views: 104

Answers (1)

mellamokb
mellamokb

Reputation: 56779

It will probably be easier to concat the values together first in a subquery, and then join them into the parent table (DEMO):

SELECT t.id, t.name, r.results, p.parameters
FROM  `test` t
LEFT JOIN  (select test_id, group_concat(result) results
            from `result` r group by test_id) r ON r.test_id = t.id
LEFT JOIN  (select test_id, group_concat(name) parameters
            from `parameter` p group by test_id) p ON p.test_id = t.id;

Result:

| ID |                NAME |                            RESULTS |          PARAMETERS |
---------------------------------------------------------------------------------------
|  1 | test w/o parameters | the first result,the second result |              (null) |
|  2 |   test w/ paramters |                             (null) | command_line,userid |

Upvotes: 1

Related Questions