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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.