Mysql Union Left Join Group Concat

Question

I need help with this Mysql query

Select id, code, Tbldetail from TblProd

The result is:

id       code            detail        (i Need ADD Model Column Here)
100    432320-1         Keyboard
101    432325-1         Mouse
102    432329-1         Printer

I have another table with this structure:

id     code             model 
1     432320-1           rx1
2     432320-1           rx2
3     432320-1           rx3
4     432325-1           rx1
5     432329-1           rx2
6     432329-1           rx1

I need an output like this:

id       code            detail               Model
100    432320-1         Keyboard          rx1,rx2,rx3
101    432325-1         Mouse             rx1
102    432329-1         Printer           rx2,rx1

How can I perform this?

Very hard to read. Can you format it a bit better, please? – T Gray Apr 18 '16 at 18:20 — T Gray, Apr 18 '16 at 18:20

Barmar · Answer 1 · 2016-04-18T18:40:05.420

2

It's exactly what you say in the title:

SELECT p.id, p.code, p.detail, GROUP_CONCAT(m.model ORDER BY m.model) as model
FROM TblProd AS p
LEFT JOIN TblModel AS m ON p.code = m.code
GROUP BY p.id

edited Apr 18 '16 at 18:40

answered Apr 18 '16 at 18:31

Barmar

741,623
53
500
612

Adding the DISTINCT keyword in the GROUP_CONCAT would return the same result, at least for the specified example. Adding an ORDER BY to the GROUP_CONCAT would make the return value more deterministic. (Without that, MySQL could return `'rx2,rx3,rx1'` for the GROUP_CONCAT. The specification doesn't mention any requirement about not returning duplicates, and the order of the model values may not be important. +10. – spencer7593 Apr 18 '16 at 18:39
@spencer7593 While there may be applications where `DISTINCT` is needed, this doesn't look like it. But getting consistent ordering is reasonable, so I've added that to my answer. – Barmar Apr 18 '16 at 18:41

score 0 · Answer 2 · answered Apr 18 '16 at 18:34

There's a couple of ways to get that result.

One alternative to a LEFT JOIN is to use a correlated query in the SELECT list.

 SELECT t.id
      , t.code
      , t.detail
      , ( SELECT GROUP_CONCAT(DISTINCT r.model ORDER BY r.model)
            FROM TblModel r
           WHERE r.code = t.code
        ) AS model  
  FROM TblProd t
 ORDER BY t.id

score 0 · Answer 3 · answered Apr 18 '16 at 18:34

0

SELECT a.id, a.code, a.detail, GROUP_CONCAT(b.model) as model
FROM TblProd AS a,TblModel AS b ON a.code = b.code
GROUP BY a.id

answered Apr 18 '16 at 18:34

Prabhat Sinha

1,500
20
32

2

Isn't time already to ditch the old-school comma operator the inner join operation, and use the JOIN keyword instead? Also, OP mentions making use of an outer join in the question title. – spencer7593 Apr 18 '16 at 18:41
Really Thanks for your Help – Peter013 Apr 18 '16 at 19:02
You can improve your answer by including an explanation of why this solves the problem. – simbabque Apr 19 '16 at 08:36

Mysql Union Left Join Group Concat

3 Answers3