Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Help with SQL statement combining two statements

Posted on 2011-09-23
5
Medium Priority
?
405 Views
Last Modified: 2012-05-12
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
Comment
Question by:elliottbenzle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 60

Expert Comment

by:HainKurt
ID: 36588450
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
 
LVL 60

Expert Comment

by:HainKurt
ID: 36588457
can you please give sample data
0
 
LVL 60

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 36588466
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
 
LVL 60

Expert Comment

by:HainKurt
ID: 36588475
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
 
LVL 4

Author Closing Comment

by:elliottbenzle
ID: 36588728
Thanks, I'm going to try to reword the questions as I think I simplified it too much.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question