SQL Statement join where if one condition is not met, then do the other one

Question

Lets say I have two tables

NAME_TABLE

ID1	ID2	NAME
A	1	JACK
B	2	CRAIG
C	3	RYAN
D	4	LARRY

JOB_TABLE

ID1	ID2	JOB
A	2	ENGINEER
B	1	TEACHER
E	3	ANALYST
F	4	ARCHITECT

I want to make a query that first checks compares the ID1 of each table, and join them where they match. If there is no match in ID1, then we compare the ID2s.

So the final table would look like this. Notice that if the ID1s match, we don't care about doing anything with the ID2. We only care about ID2 if there is no ID1 match:

FINAL_TABLE

NAME	JOB
JACK	ENGNEER
CRAIG	TEACHER
RYAN	ANALYST
LARRY	ARCHITECT

Gordon Linoff · Accepted Answer · 2021-01-31 03:19:33Z

You can use two left joins:

select nt.*, coalesce(jt1.job, jt2.job) as job from name_table nt left join job_table jt1 on nt.id1 = jt1.id1 left join job_table jt2 on nt.id2 = jt2.id2 and jt1.id1 is null;

Collectives™ on Stack Overflow

SQL Statement join where if one condition is not met, then do the other one

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related