I have 3 tables and I am trying to get unique results from all 3 tables (including other columns from each table).
I have tried union approach but that approach only works when I have single column selected from each table.
As soon as I want another corresponding column value from each table, I don't get unique values for the field I am trying to get.
Here is the example tables i have created.
CREATE TABLE TABLEA
(id int, city varchar(6))
;
INSERT INTO TABLEA
([id], [city])
VALUES
(1, 'A'),
(2, 'B'),
(3, 'C')
;
CREATE TABLE TABLEB
(id int, city varchar(6))
;
INSERT INTO TABLEB
([id], [city])
VALUES
(1, 'B'),
(2, 'C'),
(3, 'D')
;
CREATE TABLE TABLEC
(id int, city varchar(6))
;
INSERT INTO TABLEC
([id], [city])
VALUES
(1, 'C'),
(2, 'D'),
(2, 'E')
;
Desired result: A,B,C,D,E
unique city from all 3 table combined. By unique, I am referring to DISTINCT city from the combination of all 3 tables. Yes, the id is different for common values between tables but it doesn't matter in my use-case if id is coming from table A, B OR C, as long as I am getting DISTINCT (aka UNIQUE) city across all 3 tables
Tried this query but not luck (city B is missing in the output)
SELECT city, id FROM (
SELECT city, id FROM TABLEA
where city not in (select city from TABLEB UNION select city from TABLEC)
UNION
SELECT city, id FROM TABLEB
where city not in (select city from TABLEA UNION select city from TABLEC)
UNION
SELECT city, id FROM TABLEC
) as mytable