MySQL combine two columns into rows

Question

I'm doing this query

SELECT 
ub_properties.title, 
ub_level_two.level_two AS level_two, 
ub_autocomplete.level_two AS level_two_extra, 
ub_utility.utility  AS utility, 
ub_autocomplete.utility AS utility_extra 
FROM ub_properties 
LEFT JOIN ub_meta ON ub_properties.post_id=ub_meta.post_id 
LEFT JOIN ub_level_two ON ub_meta.level_two_id=ub_level_two.id 
LEFT JOIN ub_utility ON ub_meta.utility_id=ub_utility.id 
LEFT JOIN ub_autocomplete ON ub_meta.autocomplete_id=ub_autocomplete.id 
WHERE ub_properties.user_id=1

and getting this result:

Notice columns level_two and level_two_extra -they should be in the same column. So in essence I need to to combine the results of these two columns in one column (will result in more rows in result set).

I can't use CONCAT here because for technical reasons I want them each in its own row.

Any suggestion to point me in the right direction will be much appreciated. Been searching all over the place with no success so far.

---- Edit, SOLUTION ---- ( thanks @drpetermolnar )

SELECT 
ub_properties.title, 
ub_level_two.level_two, 
ub_utility.utility
FROM ub_properties 
LEFT JOIN ub_meta ON ub_properties.post_id=ub_meta.post_id 
LEFT JOIN ub_level_two ON ub_meta.level_two_id=ub_level_two.id 
LEFT JOIN ub_utility ON ub_meta.utility_id=ub_utility.id 
WHERE ub_properties.user_id=1
UNION
SELECT 
ub_properties.title, 
ub_autocomplete.level_two, 
ub_autocomplete.utility 
FROM ub_properties 
LEFT JOIN ub_meta ON ub_properties.post_id=ub_meta.post_id 
LEFT JOIN ub_autocomplete ON ub_meta.autocomplete_id=ub_autocomplete.id
WHERE ub_properties.user_id=1

because at the next step I will be wanting to get the IDs as well as the text in those fields (that I'm getting now). Which means I will need to get not only one field from the ub_level_two table, but probably 2-3 fields and combine them with 2-3 corresponding fields from ub_autocomplete table. So yep, right now I can use CONCAT, but at a later step I will need to combine them into rows anyways. That's how I see it, maybe that's wrong but... :) — Altin
– Altin, Commented Nov 11, 2015 at 13:41

drpetermolnar · Accepted Answer · 2015-11-11 03:13:17Z

1

Use the UNION keyword https://dev.mysql.com/doc/refman/5.7/en/union.html Write two SELECT statements that only differ in one place: the first uses level_two, the second uses level_two_extra. Combine with UNION.

answered Nov 11, 2015 at 3:13

drpetermolnar

1794 bronze badges

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

Collectives™ on Stack Overflow

MySQL combine two columns into rows

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