Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • 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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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