Improve company productivity with a Business Account.Sign Up

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

MySQL - help with concat Select statement

I have a table like this:

home_nation        company_nation
CANADA
USA
                             CANADA
                             BERMUDA
                             IRELAND
CANADA              CANADA
                             CANADA
BERMUDA             BERMUDA
BERMUDA
                             USA
BERMUDA
DENMARK             DENMARK

I want to return only unique values from both fields, my current SQL statement looks like this:
SELECT DISTINCT (CONCAT( home_nation, company_nation ))uniqFROM jos_users

Which is very close. It is returning only unique values, but when both fields have a value (like CANADA/CANADA) then the results returned has the country repeated twice, so the current results look like this:

uniq
CANADA
USA
BERMUDA
IRELAND
CANADACANADA
CANADA
BERMUDABERMUDA
DENMARKDENMARK

Is there a way to deal with the double entry problem?

Thanks
     
0
elliottbenzle
Asked:
elliottbenzle
  • 2
1 Solution
 
ute_arbeitCommented:
Hi,

if the empty fields are NULL then the statement would be:

SELECT IFNULL(home_nation,company_nation) FROM jos_users;

Cheers
0
 
_agx_Commented:
IF I'm understanding correctly .. this will return the unique nations in either column

   SELECT DISTINCT home_nation AS uniq  FROM jos_users
   UNION
   SELECT DISTINCT company_nation AS uniq  FROM jos_users

Just exclude the empty values. I'm assuming an empty string.  If not adjust the filter accordingly


SELECT t.Uniq
FROM   (
           SELECT DISTINCT home_nation AS uniq  FROM jos_users
           UNION
           SELECT DISTINCT company_nation AS uniq  FROM jos_users
      ) t
WHERE t.Uniq <> ''



   SELECT DISTINCT home_nation AS uniq  FROM jos_users
   UNION
   SELECT DISTINCT company_nation AS uniq  FROM jos_users
0
 
_agx_Commented:
Sorry. ignore the last 3 lines. it's a copy paste error.  The final query is just:

SELECT t.Uniq
FROM   (
           SELECT DISTINCT home_nation AS uniq  FROM jos_users
           UNION
           SELECT DISTINCT company_nation AS uniq  FROM jos_users
      ) t
WHERE t.Uniq <> ''

Open in new window

0
 
elliottbenzleAuthor Commented:
Thanks. That was very helpful.
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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