List records do not exist in other table

Hi,

I'm trying to list all the records in a table that do not exist in the other table. The table that has the records is Obr and the table where records are missing is ObrLin. However I am not getting the expected result. It may be because the field I want to compare is different in the two tables. At first the field is "Idstamp" and the second is "Idstampobr"

 SELECT Obr.Cobr, Obr.Cf, Obr.ent, Obr.Idstamp,Obr.Temp FROM Obr
  WHERE Obr.Idstamp NOT IN(SELECT Obr.Idstamp FROM ObrLin WHERE ObrLin.IdStampObr IS NOT NULL)
order by Cobr
rflorencioAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
SELECT Obr.Cobr, Obr.Cf, Obr.ent, Obr.Idstamp,Obr.Temp
FROM Obr
WHERE NOT EXISTS(SELECT 1 FROM FROM ObrLin WHERE ObrLin.IdStampObr = Obr.Idstamp)
ORDER BY Cobr
0
 
Jan LouwerensSoftware EngineerCommented:
Without knowing the table schemas, and what the foreign key references are, I can only guess at what you're trying to do. But here is one guess:

SELECT Obr.Cobr, Obr.Cf, Obr.ent, Obr.Idstamp,Obr.Temp FROM Obr
  WHERE Obr.Idstamp NOT IN(SELECT Obr.IdStampObr FROM ObrLin WHERE ObrLin.IdStampObr IS NOT NULL)
order by Cobr

Open in new window

0
 
Ryan ChongCommented:
or using a Left join ... where ... is null version:

SELECT Obr.Cobr, Obr.Cf, Obr.ent, Obr.Idstamp,Obr.Temp FROM Obr
  LEFT JOIN ObrLin ON Obr.Idstamp = ObrLin.IdStampObr
WHERE ObrLin.IdStampObr IS NULL
order by Obr.Cobr

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.