Selecting Data from one table that doesnt match another table

Transact-SQL
1

I have 2 tables One called CData and another called Elig. CData has 11000 rows and Sheet has 19806. When joined together 10000 rows match on inner join. I want find the 1000 rows that didnt match in the CDATA table.

Columns needed:
FirstName, LastName, DOB, AGE, Address, CIty State, ZIP

I used the Firstname, LastName and DOB to join the 2 tables. But Again I need to find the rows that are in CData and not in Elig. Hope that made since. Thanks

2

You can use NOT EXISTS construct, or EXCEPT construct - see below

-- not exists construct
select * from CData as c
where not exists
(
	select * from Elig as e
	where 
		e.FirstName = c.FirstName
		and e.LastName = c.LastName
		and e.DOB = c.DOB
		and e.Age = c.Age
		and e.Address = c.Address
		and e.City = c.City
		and e.State = c.State
		and e.ZIP = c.ZIP
);

-- except construct
select FirstNamae, LastName, DOB /* your other columns */
from CData

EXCEPT

select FirstNamae, LastName, DOB /* your other columns */
from Elig
2 Likes
3 
SELECT c.FirstName, c.LastName, c.DOB, c.AGE, c.Address, c.CIty c.State, c.ZIP
FROM CData c
LEFT JOIN Elig e ON c.Firstname=e.Firstname
                AND c.LastName=e.LastName 
                AND c.DOB =e.DOB
WHERE e.FirstName IS NULL;
2 Likes
4

Thank you

5

Glad I could help

1 Like