how to select distinct value from multiple tables

database, mysql, sql

Solution

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   ║
╚════════╝

Problem

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.

Original source