By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,401 Members | 539 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,401 IT Pros & Developers. It's quick & easy.

One to Many with a "Not In" component

P: 1
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
Share this Question
Share on Google+
1 Reply

ck9663
Expert 2.5K+
P: 2,878
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.