Syntax for SQL Not In List?

Question

I am trying to develop a T-SQL query to exclude all rows from another table "B". This other table "B" has 3 columns comprising its PK for a total of 136 rows. So I want to select all columns from table "A" minus those from table "B". How do I do this? I don't think this query is correct because I am still getting a duplicate record error:

CREATE TABLE #B (STUDENTID VARCHAR(50), MEASUREDATE SMALLDATETIME, MEASUREID VARCHAR(50))
INSERT #B 
SELECT studentid, measuredate, measureid
  from [J5C_Measures_Sys] 
GROUP BY studentid, measuredate, measureid
  HAVING COUNT(*) > 1

insert into J5C_MasterMeasures (studentid, measuredate, measureid, rit)
select A.studentid, A.measuredate, B.measurename+' ' +B.LabelName, A.score_14
from [J5C_Measures_Sys] A
join [J5C_ListBoxMeasures_Sys] B on A.MeasureID = B.MeasureID 
  join sysobjects so on so.name = 'J5C_Measures_Sys' AND so.type = 'u'
join syscolumns sc on so.id = sc.id and sc.name = 'score_14' 
join [J5C_MeasureNamesV2_Sys] v on v.Score_field_id = sc.name
 where a.score_14 is not null AND B.MEASURENAME IS NOT NULL
 and (A.studentid NOT IN (SELECT studentid from #B) 
and a.measuredate NOT IN (SELECT measuredate from #B)
and a.measureid NOT IN (SELECT measureid from #B))

Answer 1

use NOT EXISTS...NOT IN doesn't filter out NULLS

insert into J5C_MasterMeasures (studentid, measuredate, measureid, rit)
select A.studentid, A.measuredate, B.measurename+' ' +B.LabelName, A.score_14
from [J5C_Measures_Sys] A
join [J5C_ListBoxMeasures_Sys] B on A.MeasureID = B.MeasureID 
  join sysobjects so on so.name = 'J5C_Measures_Sys' AND so.type = 'u'
join syscolumns sc on so.id = sc.id and sc.name = 'score_14' 
join [J5C_MeasureNamesV2_Sys] v on v.Score_field_id = sc.name
 where a.score_14 is not null AND B.MEASURENAME IS NOT NULL
 AND NOT EXISTS (select 1 from  #B where #b.studentid = A.studentid
 and a.measuredate = #B.measuredate
 and a.measureid = #B.measureid)
  and not exists (select 1 from J5C_MasterMeasures z 
                  where z.studentid = A.studentid)

Just so you know, take a look at Select all rows from one table that don't exist in another table

Basically there are at least 5 ways to select all rows from onr table that are not in another table

• NOT IN
• NOT EXISTS
• LEFT and RIGHT JOIN
• OUTER APLY (2005+)
• EXCEPT (2005+)

Answer 2

I think you can use "NOT IN" with a subquery, but you say you have a multi-field key?

I'd be thinking about using a left outer join and then testing for null on the right...

Martin.

Answer 3

Here is a general solution for the difference operation using left join:

select * from FirstTable
left join SecondTable on FirstTable.ID = SecondTable.ID
where SecondTable.ID is null

Of course yours would have a more complicated join on clause, but the basic operation is the same.