CODEWITHSUNDEEP
sqlsql-serverdatabase
kuml2
kuml2

Reputation: 125

How to get unique records from 3 tables

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.

Sample Database and query available here as well: http://www.sqlfiddle.com/#!18/1b9a6/10

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.

I tried this query but no 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

Upvotes: 0

Views: 2560

Answers (2)

T. Peter
T. Peter

Reputation: 887

try this. As this should give you distinct city with there first appear id:

select distinct min(id) over(partition by city) id, city  from (
select * from TABLEA
union all 
select * from TABLEB
union all
select * from TABLEC ) uni

Upvotes: 1

Nick Scriabin
Nick Scriabin

Reputation: 301

You got the right idea, just wrap the UNION results in a subquery/temp table and then apply the DISTINCT

WITH TABLEE AS (
SELECT city, id FROM TABLEA
UNION
SELECT city, id FROM TABLEB
UNION
SELECT city, id FROM TABLEC
)
    
SELECT DISTINCT city
FROM TABLEE

Upvotes: 0

Related Questions