SQL NOT IN Clause

.net, notin, sql, sql-server-2005, t-sql

Solution

you have a NULL value in the table

try this

SELECT id, name 
FROM vw_x 
WHERE id NOT IN (select pid from table_x where pid is not null)
GROUP BY id, name
Having max(c_date) > GETDATE()

or this

SELECT id, name 
FROM vw_x 
WHERE  NOT EXISTS (select 1 from table_x  where pid = vw_x.id  )
GROUP BY id, name
Having max(c_date) > GETDATE()

See also Select all rows from one table that don't exist in another table

Problem

I have a query which is not working as expected ``` Q1: SELECT id, name FROM vw_x WHERE id NOT IN (select pid from table_x) GROUP BY id, name Having max(c_date) > GETDATE() Q2: SELECT id, name FROM vw_x GROUP BY id, name Having max(c_date) > GETDATE() ``` Q1 is not returning anything even though i know those ids are not in table_x Q2 runs correctly without NOT IN What could be wrong with my query?

Original source