469,925 Members | 1,578 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,925 developers. It's quick & easy.

One to Many with a "Not In" component

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.
Sep 11 '07 #1
1 1142
ck9663
2,878 Expert 2GB
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?

Expand|Select|Wrap|Line Numbers
  1. select tablea.field1, tableb.field2
  2. from table inner join tableb on tablea.key = tableb.key and tableb.columnwithxy in ('X','Y')
Sep 11 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Torben Laursen | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.