Selecting data based on 2 tables

[slr]

What I am trying to do is to write a query that finds all records in Table ADS that do not have a corrisponding record in the tables PROOFS. Help

SELECT *
FROM Ads
WHERE AdNums NOT IN
(SELECT Proofs.AdNums
FROM proofs))

Ads
AD_ID Long
RevisionDate Date
AdNums long

Proofs
Ad_ID
.....
AdNums long


Ads

[dRAMmer]

Your query should be working. What problems are you having?

[RobDog888]

Try this...

SELECT *
FROM Ads
WHERE Ads.AdNums NOT EXISTS
(SELECT Proofs.AdNums
FROM proofs))

From SQL BOL:

When a subquery is introduced with the keyword EXISTS, it
functions as an existence test. The WHERE clause of the outer
query tests for the existence of rows returned by the subquery.
The subquery does not actually produce any data; it returns a
value of TRUE or FALSE.

[dRAMmer]

Originally posted by RobDog888
Try this...

SELECT *
FROM Ads
WHERE Ads.AdNums NOT EXISTS
(SELECT Proofs.AdNums
FROM proofs))

This will not work. What slr have should be working, if we want to try your code, we should do this:

SELECT *
FROM Ads
WHERE NOT EXISTS
(SELECT Proofs.AdNums
FROM proofs WHERE Ads.AdNums = Proofs.AdNums)

Also, I suggest that you name your columns differently next time. Say Ads.AdNums, Proofs.RefAdNums.

Also (again), this might just be typo error but check your code again:

Originally posted by slr
What I am trying to do is to write a query that finds all records in Table ADS that do not have a corrisponding record in the tables PROOFS. Help

SELECT *
FROM Ads
WHERE AdNums NOT IN
(SELECT Proofs.AdNums
FROM proofs))

Ads
AD_ID Long
RevisionDate Date
AdNums long

Proofs
Ad_ID
.....
AdNums long


Ads

you have unmatched parenthesis, again, it might just be typo error (here in the forum), but it won't work in your query.

[Ecniv]

SELECT *
FROM Ads
WHERE AdNums NOT IN
(SELECT Proofs.AdNums
FROM proofs))

Select * from Ads left join AdNums on ads.[idfield]=adnums.[idfield] where adnums.[idfield] is null

id field in ads and in adnums are the two related fields.


Vince