Filter Out unmatching records between two tables - Ac

Hi there,
I have the following scenario.
Table_A (col1, col2, col3, id)
Table_B (col1, col2, col3, col4)

Table_A has the following records:
v11, v12, v13, 1
v21, v22, v23, 2
...
v71, v72, NULL, 7...
v91, NULL, NULL, 9
vx1, vx2, NULL, xx

Table_B has the following:
v11, v12, v13, v14
v21, v22, v23, v24
...
v71, v72, NULL, v74...
v91, NULL, NULL, NULL


The requirement is to compare first three columns (col1, col2, col3) on
those two tables and return records that have no matching from Table_A.
For example, this record (vx1, vx2, NULL, xx) from Table_A has no matching
from Table_B, it should return its ID.

I tried CASE..., JOIN...but can't seem to be able to filter out the ones I
want and ended up returning the ones that find matches as well.

Suggestions?

Thanks....a

Filter Out unmatching records between two tables - dave ballantyne

Try a left outer join


http://www.xaprb.com/blog/2005/09/23/how-to-write-a-sql-exclusion-join/

Dave

Filter Out unmatching records between two tables - Paddy

In 2005 there is the 'Except' clause.

See BOL extract below

The following query returns any distinct values from the query to the
left of the EXCEPT operand that are not also found on the right query.


GO
SELECT ProductID
FROM Production.Product
EXCEPT
SELECT ProductID
FROM Production.WorkOrder ;

Filter Out unmatching records between two tables - Stuart Ainsworth

SELECT a.ID
FROM TableA  a LEFT JOIN TableB b
ON a.col1 =3D b.col1 AND a.col2=3Db.col2 AND a.col3=3Db.col3
WHERE b.col1 IS NULL AND b.col2 IS NULL AND b.col3 ISNULL

It gets a bit tricky because you have NULL's in some of your joining
columns. You may need to change the ON section of the JOIN clause to
something like:
ON COALESCE(a.col1,'') =3D COALESCE (b.col1,'') AND.... (etc).

Stu

Filter Out unmatching records between two tables - Ac

Thank you all for response.
Yes, it can get tricky as NULL value is considered valid.
Basically, Table_B has all valid mappings (including NULL columns) and I
would like to filter out records from Table_A that has no matchings in
Table_B.

Filter Out unmatching records between two tables - Razvan Socol

Try something like this:

SELECT id FROM Table_A a WHERE NOT EXISTS (
SELECT * FROM Table_B b
WHERE (a.col1=b.col1 OR a.col1 IS NULL AND b.col1 IS NULL)
AND (a.col2=b.col2 OR a.col2 IS NULL AND b.col2 IS NULL)
AND (a.col3=b.col3 OR a.col3 IS NULL AND b.col3 IS NULL)
)

--
Razvan Socol
SQL Server MVP

Filter Out unmatching records between two tables - Stuart Ainsworth

n
.
hing
es I
=3Db.col3
(etc).

Did you try what I suggested (including the COALESCE options in the
JOIN)?