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?
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