Mysql rows into columns, optimization query using IF
I have this following query:
SELECT a.rank AS rank, concat( m.prenom, ' ', m.nom ) AS name, d.department
FROM `0_area` AS a
LEFT JOIN 0_member AS m ON a.user_id = m.id
LEFT JOIN 0_depart AS d ON a.user_id = d.user_id
WHERE a.sec_id = 2
AND (a.rank = 'mod' OR a.rank = 'adm')
AND d.department IN ( 75, 92 )
The above query is returning me three columns, naming rank | name | department
and it's working, with a separate row for each rank against a department.
rank | name | department
mod | Steven Smith | 75
mod | Jeremy Roy | 92
adm | Vincent Jones | 75
What I need, is to get all those information in one row, becau开发者_高级运维se I need to have it as a part of another bigger query, that is returning one row only.
I thought of group_concat, and to have two columns, one for mod and one for adm. In case there are multiple mods (in 75, 92 for the given example), they will be comma separated.
mod | adm
Steven Smith, Jeremy Roy | Vincent Jones
Have I been clear? Thanks friends.
Something like:
SELECT
a.rank AS rank,
concat( m.prenom, ' ', m.nom ) AS name,
GROUP_CONCAT(d.department ORDER BY d.department ASC SEPARATOR '|')
FROM
`0_area` AS a
LEFT JOIN
0_member AS m
ON
a.user_id = m.id
LEFT JOIN
0_depart AS d
ON
a.user_id = d.user_id
WHERE
a.sec_id = 2
AND
(a.rank = 'mod' OR a.rank = 'adm')
AND
d.department IN ( 75, 92 )
GROUP BY
a.rank
Some googling told me that I need this sql:
SELECT
GROUP_CONCAT( if( a.rank='mod', concat( m.prenom, ' ', m.nom ), '' ) ) AS 'mod',
GROUP_CONCAT( if( a.rank='adm', concat( m.prenom, ' ', m.nom ), '' ) ) AS 'adm'
FROM `0_area` AS a
LEFT JOIN 0_member AS m ON a.user_id = m.id
LEFT JOIN 0_depart AS d ON a.user_id = d.user_id
WHERE a.sec_id =2
AND (a.rank = 'mod' OR a.rank = 'adm' )
AND d.department IN ( 75, 92 )
GROUP BY a.sec_id
@ToonMariner This solves the problem for this query, however, I will still need to put two queries together. Probably I will create a new post for this one.
精彩评论