How to LEFT JOIN with multiple cases-CodePudding

Assuming I have these two tables like:

Table: tab1

valA	valB	valC	valD
1	11	111	A
2	11	333	A
3	44	444	B
3	66	666	D

Table: tab2

val1	val2	val3	val4
1	11	111	A
1	22	222	A
2	44	333	B
3	55	555	A
3	66	666	D

I have 3 cases that I have unite with UNION. This is the code:

WITH tog AS
(

SELECT* FROM tab1
INNER JOIN tab2
ON ValA = Val1
AND ValB = Val2 

UNION

SELECT* FROM tab1
INNER JOIN tab2
ON ValA = Val1
AND ValB <> Val2  
AND ValD = Val4

UNION

SELECT* FROM tab1
INNER JOIN tab2
ON ValA = Val1
AND ValC = Val3
AND ValD <> Val4
)

The result would be:

table: tog

valA	valB	valC	valD	val1	val2	val3	val4
1	11	111	A	1	11	111	A
1	11	111	A	1	22	222	A
2	33	333	a	2	44	444	B
3	66	666	D	3	66	666	D

So, I need the table of tab1 but without the matches, like:

valA	valB	valC	valD	val1	val2	val3	val4
3	44	444	B

I tried something like this, but I know this is totaly wrong:

SELECT * FROM tab1
LEFT JOIN tog
ON tab1.valA = tog.val1
WHERE tog.val1 IS NULL

I am pretty new on SQL so I would appreciate any tipps and suggestions.

CodePudding user response：

You want to select data from tab1 where not exists a match in tab2.

select *
from tab1
where not exists
(
  select null
  from tab2
  where tab1.vala = tab2.val1
  and 
  ( 
    (tab1.valb = tab2.val2) or
    (tab1.vald = tab2.val4 and tab1.valb <> tab2.val2) or
    (tab1.valc = tab2.val3 and tab1.vald <> tab2.val4)
  )
);

Or, if you need the empty tab2 columns:

select *
from tab1
left outer join tab2
  on tab1.vala = tab2.val1
  and 
  ( 
    (tab1.valb = tab2.val2) or
    (tab1.vald = tab2.val4 and tab1.valb <> tab2.val2) or
    (tab1.valc = tab2.val3 and tab1.vald <> tab2.val4)
  )
)
where tab2.val1 is null; -- dismiss the matches