One to Many with a "Not In" component

[kevinmccarthy]

Hello,

I'm trying to compose a sql statement that will select some columns from a parent table and get the child records that DO NOT contain a certain value.

From a simple perspective I have Table "A" that has a one to many relationship with Table "b". I'm trying to get all of the records from Table "A" that only have child records from Table "b" with values of "X" and "Y" but not "Z".

So Table "A" has a record of "Joe Smith". "Joe Smith" can have children records from Table "b" of "X", "Y" and "Z". He would not qualify ffor retrieval. However, "Jane Smith" has children records of "X" and "Y" but no "Z". How did I retrieve "Jane Smith"?

thanks.

[ck9663]

Hello,

I'm trying to compose a sql statement that will select some columns from a parent table and get the child records that DO NOT contain a certain value.

From a simple perspective I have Table "A" that has a one to many relationship with Table "b". I'm trying to get all of the records from Table "A" that only have child records from Table "b" with values of "X" and "Y" but not "Z".

So Table "A" has a record of "Joe Smith". "Joe Smith" can have children records from Table "b" of "X", "Y" and "Z". He would not qualify ffor retrieval. However, "Jane Smith" has children records of "X" and "Y" but no "Z". How did I retrieve "Jane Smith"?

thanks.

will something like this work?

select tablea.field1, tableb.field2
from table inner join tableb on tablea.key = tableb.key and tableb.columnwithxy in ('X','Y')