sql - match two of the same values in different column positions

user_13315358 0
Viewed 104

I am looking to join two different tables on the id, and need to extract unique names out of each table; if one table has a certain name but the other doesn't, there should be one value and one null. This should be vice versa as well.

With joins, the current output looks like this:

id  name_1  name_2
1   max     steph
1   max     john
1   john    chris
1   john    chris
1   chris   steph
1   chris   null
1   null    max
1   null    null
1   tony    john
1   tony    max

expected output:

id  name_1  name_2
1   max     max
1   john    john
1   chris   chris
1   null    steph
1   tony    null

current sql:

select
table1.id,
table1.name as name_1,
table2.name as name_2
from table1
left join table2
on table1.id = table2.id

(snowflake)

snowflake-cloud-data-platform
4 Answers
SELECT
   NVL(d1.id, d2.id) as id,
   d1.name as name_1,
   d2.name as name_2
FROM (
     SELECT DISTINCT id,name FROM table1
) AS d1
FULL OUTER JOIN (
     SELECT DISTINCT id,name FROM table2
) AS d2
    ON d1.id = d2.id AND d1.name = d2.name
ORDER BY 1, (d1.name,d2.name)

This takes the distinct id,name pairs from both table, then full outer joins those sets of values. Thus if the id,name are in both they match. And if they don't match they are still keep.

So with these CTE's providing the fake data:

WITH table1(id,name) AS (
    select * from values (1,'aa'),(1,'ab'),(2,'ba')
), table2(id,name) AS (
    select * from values (1,'aa'),(1,'ac'),(2,'ba'),(2,'bb')
)
ID NAME_1 NAME_2
1 aa aa
1 ab null
1 null ac
2 ba ba
2 null bb
user_439920

Following can be used for this -

with cte as
(
select distinct t1.id,name_1 from t1)
select distinct ifnull(t2.id,cte.id) id,
cte.name_1,
t2.name_2
from t2 full outer join cte
ON cte.id=t2.id
and cte.name_1 = t2.name_2
order by cte.name_1;
+----+--------+--------+
| ID | NAME_1 | NAME_2 |
|----+--------+--------|
|  1 | chris  | chris  |
|  1 | john   | john   |
|  1 | max    | max    |
|  1 | tony   | NULL   |
|  1 | NULL   | steph  |
+----+--------+--------+
user_139153130

Add a WHERE clause.

select
table1.id,
table1.name as name_1,
table2.name as name_2
from table1
left join table2
WHERE table1.name = table2.name 
OR table1.name is null
OR table2.name is null 
on table1.id = table2.id

If you just need a list of unique names

select distinct name from table1
union
select distinct name from table2
user_187762640

Simeons answer is the way to go since snowflake supports full outer joins. But for those of you that use a relational database that lacks support for full outer joins, and have the same issue, this approach can be an alternative:

select id, 
       if(instr(group_concat(tb), 1), name, NULL) name_1, 
       if(instr(group_concat(tb), 2), name, NULL) name_2 
from(
    select id, name, 1 tb from table1
    union
    select id, name, 2 tb from table2
) a
group by id, name
order by name

The result:

| id  | name_1 | name_2 |
| --- | ------ | ------ |
| 1   | chris  | chris  |
| 1   | john   | john   |
| 1   | max    | max    |
| 1   | null   | steph  |
| 1   | tony   | null   |

Fake data:

CREATE TABLE table1 (
  id int(11),
  name varchar(50)
  );
  
CREATE TABLE table2 (
  id int(11),
  name varchar(50)
  );  
  
INSERT INTO table1 VALUES
    (1, 'max'),
    (1, 'john'),
    (1, 'chris'),
    (1, 'tony');

INSERT INTO table2 VALUES
    (1, 'steph'),
    (1, 'john'),
    (1, 'chris'),
    (1, 'max');

And a dbfiddle: https://www.db-fiddle.com/f/gQ4U7hu2S2EyFEtZrapqdu/6

user_13631900
Related