0

i've been trying to get records that exist in table A but not in table B (as you can see in Result) with the following query but this shows an empty table

the query:

select * FROM TableA 
where ref1 not in (select ref1 from TableB) 
and ref2 not in (select ref2 from TableB) 
and ref3 not in (select ref3 from TableB)

Table A:

ref1    ref2    ref3    qte
VT1     Jaune   L       100
VT1     Jaune   XL      100
VT1     GRIS    L       100
VT1     GRIS    XL      100
VT2     Jaune   L       100
VT2     Jaune   XL      100
VT2     GRIS    L       100
VT2     GRIS    XL      100

Table B:

ref1    ref2    ref3    qte
VT1     Jaune   L       100
VT1     GRIS    L       100
VT2     Jaune   L       100
VT2     GRIS    L       100
VT2     GRIS    XL      100

Result:

ref1    ref2    ref3    qte
VT1     Jaune   XL      100
VT2     Jaune   XL      100
0x58
  • 427
  • 5
  • 13

2 Answers2

0

Try a series of LEFT JOINs, then checking for NULL

select * 
FROM TableA 
LEFT OUTER JOIN TableB b1
ON TableA.ref1 = b1.ref1
LEFT OUTER JOIN TableB b2
ON TableA.ref2 = b2.ref2
LEFT OUTER JOIN TableB 3
ON TableA.ref3 = b3.ref3
WHERE b1.ref1 IS NULL
AND b2.ref2 IS NULL
AND b3.ref3 IS NULL

Although looking at the results you want rather than the explanation I think this might do it:-

select * 
FROM TableA 
LEFT OUTER JOIN TableB b1
ON TableA.ref1 = b1.ref1
AND TableA.ref2 = b1.ref2
AND TableA.ref3 = b1.ref3
WHERE b1.ref1 IS NULL
Kickstart
  • 21,403
  • 2
  • 21
  • 33
0

pretty simple actually...just join tables and set null for table which u want to exclude :

select * FROM TableA a
left join TableB b 
  on a.ref1 = b.ref1 
    where b.ref1 is null
NoobEditor
  • 15,563
  • 19
  • 81
  • 112