shalin shalin - 4 months ago 10
SQL Question

Combine rows in to a single row in mysql

I have a problem in merging two queries:
a

SELECT f.attribute_name, s.value as htc
FROM attributes f
left join `car_type` c on f.type_id=c.id
left join mode m on m.id = f.mode_id
left join settings s on f.id=s.attr_id
where c.id=1 and f.mode_id = 1


While giving this query, I got the following output as in image

enter image description here

SELECT f.attribute_name, s.value as ct
FROM attributes f
left join `car_type` c on f.type_id=c.id
left join mode m on m.id = f.mode_id
left join settings s on f.id=s.attr_id
where c.id=2 and f.mode_id = 1


For the second query, I got the output as in image

enter image description here

I want to get the ct and htc values in the same row where the attribute name are same in both the queries.

Answer

This seems to be a table pivot question, not sure if it works or not, but try it:

SELECT
    f.attribute_name,
    MAX(CASE WHEN c.id = 1 THEN s.value ELSE NULL END) AS htc,
    MAX(CASE WHEN c.id = 2 THEN s.value ELSE NULL END) AS ct
FROM attributes f
LEFT JOIN `car_type` c ON f.type_id=c.id
LEFT JOIN MODE m ON m.id = f.mode_id
LEFT JOIN settings s ON f.id=s.attr_id
WHERE c.id IN (1,2) AND f.mode_id = 1
GROUP BY f.attribute_name
Comments