CODEWITHSUNDEEP
sql-server-2008t-sql
wootscootinboogie
wootscootinboogie

Reputation: 8695

where not in subquery - SQL Server 2008

The inner query in the following SQL statement is to normalize part of the database (code1, code2, code3, etc.) With the outer query I want to select the codes that aren't in the lookup table (tblicd)

select primarycode from 
(
select id, primarycode from myTable
union
select id, secondarycode from myTable
union 
select id, tertiarycode from myTable) as t
order by id
where primarycode not in tblicd.icd_id

The query above doesn't run, I'm wondering what I'm doing wrong. The error I get is the multi-part identifier tblicd.icd_id could not be bound

Upvotes: 0

Views: 320

Answers (2)

YvesR
YvesR

Reputation: 6222

where primarycode not in tblicd.icd_id

might be

where primarycode not in (SELECT icd_id FROM tblicd )

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 837956

One problem is your ORDER BY and WHERE clauses are reversed. The ORDER BY clause must come after the WHERE clause.

Your WHERE clause is also incorrect. It should be like this:

WHERE primarycode NOT IN (SELECT icd_id FROM tblicd)
ORDER BY id

Upvotes: 6

Related Questions