immma immma - 2 months ago 7
MySQL Question

MySQL combine column

I have 2 tables that reference each other. First table is position.

id title
------ --------
001 a
002 b
003 c
004 d
005 e


and the second table is unit.

id status info
------ ------ ------------
001 s manager
001 o head manag
002 s programmer
003 s programmer


in status field, 's' means header, and 'o' means body.
I have try with left join with no luck.

SELECT a.id, a.title,
(CASE WHEN b.status = 's' THEN b.info END) AS header,
(CASE WHEN b.status = 'o' THEN b.info END) AS body
FROM
POSITION a LEFT JOIN
unit b
ON a.id = b.id


result is

id title header body
------ ------ ---------- ------------
001 a manager (NULL)
001 a (NULL) head manag
002 b programmer (NULL)
003 c programmer (NULL)


I wonder the result is like this

id title header body
------ ------ ---------- ------------
001 a manager head manag
002 b programmer (NULL)
003 c programmer (NULL)


thanks in advance.

Answer

You need to GROUP BY id. And you need an aggregate function (like MIN() or MAX()) for the status column.

SELECT a.id, a.title, 
MIN(CASE WHEN b.status = 's' THEN b.info END) AS header, 
MIN(CASE WHEN b.status = 'o' THEN b.info END) AS body
 FROM 
POSITION a LEFT JOIN
 unit b
ON a.id = b.id
GROUP BY a.id

sqlfiddle

Comments