Compare two tables and return unique data?
-
I need to compare the data in tbl_Reports with tbl_Names and find any unique data not matching tbl_Names.
So, tbl_Reports has changing data, and tbl_Names is constant.
-
Which fields need to be compared? Just an ID field, or several?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Here's what I have so far. The problem is when I specify 'not in', it returns nothing.....
I have purposely place one unique name in tbl_Reports to test the query..
The query must return only unique names from tbl_Reports.FName that do NOT match any Fnames found in tbl_Names........
SELECT DISTINCT col_Fname
FROM tbl_Reports r JOIN tbl_Employees e
ON r.col_Fname != e.col_Fname
Where r.col_Fname not in (select col_Fname from tbl_Employees)
-
Check out exists and left join in the books online.
-
I don't think you want the JOIN.
SELECT DISTINCT col_Fname
FROM tbl_Reports
WHERE col_Fname NOT IN (SELECT col_Fname
FROM tbl_Employees)
-
Or this way:
SELECT DISTINCT col_Fname
FROM tbl_Reports r left join tbl_Employees e
ON r.col_Fname = e.col_Fname
WHERE e.col_Fname is null
Using != in a join is an advanced technique that will rarely be required. I have never needed it.
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
No, that did not work and I've been exploring the 'EXISTS' statement without success either.....
-
Noticed that col_Fname is ambiguous, so you need to qualify it:
SELECT DISTINCT r.col_Fname
FROM tbl_Reports r left join tbl_Employees e
ON r.col_Fname = e.col_Fname
WHERE e.col_Fname is null
If that doesn't work, there's something you're not telling us
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
That worked perfect........ Thank You............
-
Declare @Names table (name varchar(25) not null primary key clustered)
Declare @Peeps table (name varchar(25) not null primary key clustered)
Insert into @Names (name) values ('remi')
Insert into @Names (name) values ('sushila')
Insert into @Names (name) values ('phil')
Insert into @Peeps (name) values ('phil')
Insert into @Peeps (name) values ('remi')
Select N.name from @Names N where not exists (Select * from @Peeps P where N.name = P.name)
-
Check out which version is faster... that always change from one table to another... and in rarely is the fastest.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply