Query to Select on 2 "one to many" relates

[gwoman]

Hi ...

I have 3 tables X, Y, & Z ..
TableX & TableY have 1 to many relates to TableZ (X>>Z<<Y) however they are not directly related to each other...
I am trying to create a query thats selects all records from TableX where TableX_ID is NOT in TableZ & where TableY_ID = 1 from TableZ;
I can't seem to get the desired results ...
Thanks in advance for any help!

gwoman

 

[Golom]

Your info is a bit sketchy but try something like

Select X.ID From TableX As X

Where X.ID NOT IN
      (Select Z.ID 
       From TableZ As Z INNER JOIN TableY As Y
            ON Z.CommonField = Y.CommonField
       Where Y.ID = 1)