Finding what does not match - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Finding what does not match

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-06-09 : 10:22:40

I have a table that has a list of all the degrees we have in our system. Then I have another table with a list of degrees, but there are some degrees that are in there that are NOT in our Master list.

How can I find all of the degrees that are not on the Master list.

This is the query that I have, but it is not working. The master list below is called Valid_Table_DEGREE.

Select Distinct
[PCS Number],
[Last Name],
[First Name],
[Middle Name],
Ext,
Degree,
Practice
From vw_monthEndProvider

LEFT Join Valid_Table_DEGREE
ON Degree <> val_code

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-09 : 10:29:38

Select Distinct
[PCS Number],
[Last Name],
[First Name],
[Middle Name],
Ext,
Degree,
Practice
From vw_monthEndProvider MEP

LEFT Join Valid_Table_DEGREE VTD
ON MEP.Degree = VTD.val_code
WHERE VTD.val_code IS NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 12:03:23

[code]
Select Distinct
[PCS Number],
[Last Name],
[First Name],
[Middle Name],
Ext,
Degree,
Practice
From vw_monthEndProvider MEP
WHERE NOT EXISTS (SELECT 1 FROM Valid_Table_DEGREE WHERE val_code=MEP.Degree)
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 12:04:29

i think it should be like below

quote:
Originally posted by raky

Select Distinct
[PCS Number],
[Last Name],
[First Name],
[Middle Name],
Ext,
Degree,
Practice
From vw_monthEndProvider MEP

LEFT Join Valid_Table_DEGREE VTD
ON MEP.Degree <> = VTD.val_code
WHERE VTD.val_code IS NULL

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-20 : 05:26:38

quote:
Originally posted by visakh16

i think it should be like below
quote:
Originally posted by raky

Select Distinct
[PCS Number],
[Last Name],
[First Name],
[Middle Name],
Ext,
Degree,
Practice
From vw_monthEndProvider MEP

LEFT Join Valid_Table_DEGREE VTD
ON MEP.Degree <> = VTD.val_code
WHERE VTD.val_code IS NULL

you are right visakh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-20 : 07:36:48

For comparisons of different methods to find missing records, see
http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources