SQLTeam.com | Weblogs | Forums

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.

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

you say you want desired result of
Desired result: A,B,C,D,E

Then your query includes city and id

Could you explain more? which columns do you want returned?

maybe this?

;with src
as
(
select *, ROW_NUMBER() OVER (
			  PARTITION BY city
			  ORDER BY city
		   ) row_num
  from (
			select * from @TABLEA union
			select * from @TABLEB union
			select * From @TABLEC
		) src
)
select id, city 
  from src
  where row_num = 1

Hi @yosiasz thank you for your response. I am getting an error with the query you posted. Getting Must declare the table variable "@TABLEA".

Desired result is two columns - city (A,B,C,D,E) and id (corresponding id to the city)

just change it to use your table names

Thanks @yosiasz your query is working.

I am trying to make sure I understand your approach correctly.

Three questions:

  1. on what basis corresponding id is selected? It is random since output is picking randomly DISTINCT city values so whatever id is related to the random pick, that id is displayed?

  2. Does placement / order of tables matter? Is the order going to define how the output looks like?

  3. If I would like to save the query in a temp table, how can i do that? I am getting an error: Incorrect syntax near the keyword 'with'.

    DROP TABLE IF EXISTS #MY_TEMP_TABLE
    SELECT city, id INTO #MY_TEMP_TABLE FROM
    (
    with src
    as
    (
    select *, ROW_NUMBER() OVER (
    PARTITION BY city
    ORDER BY city
    ) row_num
    from (
    select * from TABLEA union
    select * from TABLEB union
    select * From TABLEC
    ) src
    )
    select city,id
    from src
    where row_num = 1
    ) AS MY_TABLE

Read up and understand the following and explain to me what you have understood

ROW_NUMBER() OVER (
			  PARTITION BY city
			  ORDER BY city
		   )

From what I understand row_number() assigns a sequential integer to each row within the partition of a result set. So in this case, it will always pickup the first row for each duplicate city.

The traditional method is to use a FULL OUTER JOIN, something like so:

SELECT
    CASE which_table WHEN 'A' THEN A.id WHEN 'B' THEN B.id WHEN 'C' THEN C.id END AS id,
    CASE which_table WHEN 'A' THEN A.city WHEN 'B' THEN B.city WHEN 'C' THEN C.city END AS city /*,*/
    /* ... A.next_column ... B.next_column ... C.next_column ... */
FROM dbo.TABLEA A
FULL OUTER JOIN dbo.TABLEB B ON B.city = A.city
FULL OUTER JOIN dbo.TABLEC C ON C.city IN (A.city, B.city)
CROSS APPLY (
    SELECT CASE WHEN A.id IS NULL THEN CASE WHEN B.id IS NULL THEN 'C' ELSE 'B' END ELSE 'A' END AS which_table
) AS ca1