MySQL combine two columns into rows

debugcn Published at Dev

Altin

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

drpetermolnar

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.