Filter out entire records SQL

I have two tables: TableA and TableB

TableA Has 10 records while TableB has 8 Records.

I am trying to filter the distinct records on table B from TableA so I can then move the discrepancy to TableB.

This is a Legacy, poorly made, Database, so there is no Unique Identifiers. So they look something like this.

    TableA                               TableB
    Col1, Col2, Col3                Col1,Col2,Col3
 1    X     X    Y                    X    X   Y
 2    X     Y    Y                    X    Y   Y
 3    X     X    X

I want to filter the combination of values for each record on TableB to find the missing values that are present on TableA

Answer

You need a left join from tablea to tableb and get only the rows of tablea that do not match:

select a.*
from tablea a left join tableb b
on b.col1 = a.col1 and b.col2 = a.col2 and b.col3 = a.col3
where b.col1 is null and b.col2 is null and b.col3 is null

Advertisement

Answer