finding missing records using where not exists

[gemoon]

im trying to write a query that returns missing records. im sure this is probably a simple task, but with my minimal understanding of queries i havent been able to come up with a solution.

i have three tables.
tblA: aID, a
tblB: aID, cID
tblC: c, cID

tblA and tblC are 1:many in relationship to tblB

for every record in tblA i am trying to retrieve the cID's missing from tblB

for example if the data is:
tblA:
a aID
Red 1
Blue 2

tblB:
aID cID
1 2
1 3
2 2

tblC:
c cID
Cat 2
Dog 3

the query would retrieve that Blue is missing a Dog record in tblB.

ive searched this forum and others and think the solution lies in a "not exists" statement, but im not sure exactly how to structure it.

any advice anyone can give me would be greatly appreciated.
Ed.

 

[JonFer]

Select a, c
from tblA, tblC
Where Not Exists (Select * from tblB as b
b.aID=tblA.aID and b.cID=tblC.cID)


John

http://www.interautomateconsulting.com