Help with SQL statement combining two statements
Posted on 2011-09-23
I have a table with two columns 'home_city' & 'company_city'
During my select Query I want to take these two fields and combine them into one field called 'city'
My current SQL statement looks like this:
SELECT u.company_city AS company_city, u.home_city AS home_city FROM jos_users AS u ORDER BY city ASC, u.id ASC LIMIT 0, 50
and the Query I use to create the new 'city' field would look like this:
SELECT DISTINCT home_state city FROM jos_users UNION SELECT DISTINCT company_state FROM jos_users
I'm not sure how to combine the two, but I think it would look something like this:
SELECT (SELECT DISTINCT home_city city FROM jos_users UNION SELECT DISTINCT company_city FROM jos_users) AS city FROM jos_users AS u ORDER BY city ASC, u.id ASC LIMIT 0, 50
but this gives me an error: #1242 - Subquery returns more than 1 row
How do I do this properly?