Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

Help with SQL statement combining two statements

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?

Thanks

0
elliottbenzle
Asked:
elliottbenzle
  • 4
1 Solution
 
HainKurtSr. System AnalystCommented:
use just this

select
home_city + company_city as city
FROM jos_users AS u ORDER BY city ASC, u.id ASC LIMIT 0, 50
0
 
HainKurtSr. System AnalystCommented:
can you please give sample data
0
 
HainKurtSr. System AnalystCommented:
maybe you want this:

SELECT DISTINCT city
FROM (
SELECT DISTINCT home_city city FROM jos_users
UNION
SELECT DISTINCT company_city FROM jos_users
) AS Cities
ORDER BY city ASC LIMIT 0, 50
0
 
HainKurtSr. System AnalystCommented:
or this (may not need another select)

SELECT home_city city FROM jos_users
UNION
SELECT company_city FROM jos_users
ORDER BY city ASC LIMIT 0, 50
0
 
elliottbenzleAuthor Commented:
Thanks, I'm going to try to reword the questions as I think I simplified it too much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now