1

I'm a little out of my depth here, so please forgive. But I've been using a query to find missing records in a table that all of a sudden, the query stopped working by not returning any results, when I know that there should be results.

Basic structure of my query is:

SELECT email, tempid
FROM memberTable
WHERE appid NOT IN (SELECT appid 
                    FROM memberTableHobbies)

I had 280 rows returned, I went out and went back in and tried my query again and poof, no results. But I know that when I do an individual query against a person in my "memberTable", get their tempid, and then query that tempID in my "memberTableHobbies", that ID is not there. So it should be returning in results.

Is there something I don't realize in SQL Server 2012 that doesn't like this type of query? Is there a better way to make this query work?

Thanks!

eyettea
  • 1,376
  • 2
  • 16
  • 35
trevoray
  • 317
  • 1
  • 11
  • 28
  • 1
    You probably have a NULL in the subquery,use a COALESCE or equivalent to replace NULLs with something that you know doesnt exist in appid.http://stackoverflow.com/questions/129077/not-in-clause-and-null-values – Mihai Nov 11 '14 at 23:26

2 Answers2

2

You're close, like Mihai suggested above, you should eliminate NULLs in the subquery. Try this:

SELECT email, tempid FROM memberTable WHERE appid NOT IN
(SELECT appid FROM memberTableHobbies WHERE appid IS NOT NULL)
Rocky
  • 494
  • 6
  • 18
1
SELECT email, tempid
  FROM memberTable
 WHERE appid
NOT IN (SELECT appid
          FROM memberTableHobbies
         where appid is not null and appid <> 0)
Robert Anderson
  • 1,246
  • 8
  • 11