CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user482375
user482375

Reputation:

Return results of ID NOT IN Certain Table

I am trying to write a write and the logic I am looking for is basically. If any User IDs from Table 1 do not exist in Table 2 show those in the results. So I did:

SELECT
  UserColder.ContactName,
  UserColder.Phone,
  UserColder.Email,
  UserColder.Website,
  Country.Name,
  UserColderZIP.[ZIP Code],
  UserColderZIP.[State Abbreviation]
FROM
  dbo.UserColder
  LEFT OUTER JOIN dbo.CountryUser
    ON UserColder.ID = CountryUser.[User ID]
  INNER JOIN dbo.Country
    ON CountryUser.[Foreign ID] = Country.ID
  LEFT OUTER JOIN dbo.UserColderZIP
    ON UserColder.ID = UserColderZIP.UserColder
WHERE
  UserColder.ID NOT IN (CountryUser.[User ID])

It returns data without the Where But with the Where I am trying to get it to show all the results where the Id from UserColder don't have any records in CountryUser. Right now it is returning no results and I know ID 2 doesn't exist in there. Any idea what I am doing wrong?

Thanks!

Upvotes: 2

Views: 88

Answers (2)

Kyra
Kyra

Reputation: 5407

Just grab the information from UserColder and make sure that the UserColder.ID is not in CountryUser by checking it (select statement in where). 

SELECT
  UserColder.ContactName,
  UserColder.Phone,
  UserColder.Email,
  UserColder.Website,
  Country.Name,
  UserColderZIP.[ZIP Code],
  UserColderZIP.[State Abbreviation]
FROM
  dbo.UserColder
  INNER JOIN dbo.Country
    ON CountryUser.[Foreign ID] = Country.ID
  LEFT OUTER JOIN dbo.UserColderZIP
    ON UserColder.ID = UserColderZIP.UserColder
WHERE
  UserColder.ID NOT EXISTS (select [user id] from dbo.CountryUser where CountryUser.[User ID])

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270657

Since you have a LEFT OUTER JOIN, to get records from UserColder that don't exist in CountryUser, check for NULL. The joined rows which have no corresponding value will return NULL.

WHERE
  CountryUser.[User ID] IS NULL

Upvotes: 4

Related Questions