asked on

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!

magento

Try this.

    SELECT home_nation + ' ' + company_nation AS unique_nation
    FROM jos_user

Open in new window

JestersGrind

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

HainKurt

try:

select distinct country from (
SELECT DISTINCT home_nation Country FROM jos_users
UNION
SELECT DISTINCT company_nation FROM jos_users
)

HainKurt

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...

ASKER CERTIFIED SOLUTION

magento

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

or, if its really "NULL" in either column, you could use COALESCE(home_nation, company_nation) ...

elliottbenzle

ASKER

Thanks, that did the trick.