how to select distinct value from multiple tables

Question

I need to get distinct values from 3 tables.

When I perform this code:

select DISTINCT(city) from a,b,c  

I get an error which says that my column 'city' is ambiguous.

Also I have tried this:

select DISTINCT(city) from a NATURAL JOIN b NATURAL JOIN c 

With this code I receive nothing from my tables.

Let me show you on the example of what I am trying to do:

TABLE A               TABLE B                TABLE C id  |  city           id  |  city            id  | city 1   |  Krakow         1   |  Paris           1   | Paris 2   |  Paris          2   |  London          2   | Krakow 3   |  Paris          3   |  Oslo 4   |  Rome 

And I need to get result like this

RESULTS  city  ----  Krakow  Paris  Rome  London  Oslo 

Order of the cities is not important to me I just need to have them all, and there should be only one representation of each city.

Any idea? I was thinking to use id's in the JOIN but there are not connected so I can't use that.

Answer 1

The UNION keyword will return unique records on the result list. When specifying ALL (UNION ALL) will keep duplicates on the result set, which the OP don't want.

SELECT city FROM tableA UNION SELECT city FROM tableB UNION SELECT city FROM tableC 

• SQLFiddle Demo

RESULT

╔════════╗ ║  CITY  ║ ╠════════╣ ║ Krakow ║ ║ Paris  ║ ║ Rome   ║ ║ London ║ ║ Oslo   ║ ╚════════╝ 

Answer 2

SELECT city FROM A UNION DISTINCT SELECT city FROM B UNION DISTINCT SELECT city FROM C