• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

SQL - Join to columns as one

My current SQL statement is this:
SELECT DISTINCT home_nation, company_nation
FROM jos_users

which returns to me two columns with a listing of countries:

What I want to do is only have my SQL statement return one column of unique columns. So for example:

current return:

home_nation        company_nation
Australia
Canada
                              Canada
                              Bermuda
United Kingdom
                              Australia




Return I want:

unique_nation
Australia
Canada
Canada
Bermuda
United Kingdom
Australia


Thanks for any help!

0
elliottbenzle
Asked:
elliottbenzle
1 Solution
 
magentoCommented:

Try this.

    SELECT home_nation + ' ' + company_nation AS unique_nation
    FROM jos_user

Open in new window

0
 
JestersGrindCommented:
If there is always one entry in one of the two columns, you could do something like this.

SELECT CASE WHEN home_nation = '' THEN company_nation ELSE home_nation END AS unique_nation
FROM jos_user

Greg

0
 
HainKurtSr. System AnalystCommented:
try:

select distinct country from (
  SELECT DISTINCT home_nation Country FROM jos_users
  UNION
  SELECT DISTINCT company_nation FROM jos_users
)
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
HainKurtSr. System AnalystCommented:
What I want to do is only have my SQL statement return one column of unique columns

and

Return I want:

unique_nation
Australia
Canada
Canada
Bermuda
United Kingdom
Australia

contradicts... you should say


Return I want:

unique_nation
Australia
Canada
Bermuda
United Kingdom
Australia

which I posted above...
0
 
magentoCommented:
Tested the below solution.

select concat(home_nation,company_nation) uniq from jos_users
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
or, if its really "NULL" in either column, you could use COALESCE(home_nation, company_nation) ...
0
 
elliottbenzleAuthor Commented:
Thanks, that did the trick.
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

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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