Combine rows in to a single row in mysql

Question

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

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

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

pls don't use screenshots

e4c5
– e4c5

2016-07-19 08:08:26 +00:00
Commented Jul 19, 2016 at 8:08 — e4c5
– e4c5, Commented Jul 19, 2016 at 8:08
Change WHERE to AND. Change AND to WHERE.

Strawberry
– Strawberry

2016-07-19 08:34:20 +00:00
Commented Jul 19, 2016 at 8:34 — Strawberry
– Strawberry, Commented Jul 19, 2016 at 8:34

Blank · Accepted Answer · 2016-07-19 08:18:01Z

2

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

answered Jul 19, 2016 at 8:18

Blank

12.4k1 gold badge16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Combine rows in to a single row in mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related