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

Using And in a query using two tables linked by ID

P: 1
I need to run a query showing what families have a student in both grade 5 and 7. I can run the query showing if a family either has a student in either of those grades but I need to know which family has a student in both of these grades. Is there a way to find this out. The parent table and student table is linked by family ID. Any ideas?
Oct 23 '06 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 1,892
Post table schema and the query you're trying.
Oct 23 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
SELECT ParentName, Grade
FROM Parents INNER JOIN Students
ON Parents.ParentID=Students.ParentID
WHERE Grade IN (5,7);
Oct 23 '06 #3

NeoPa
Expert Mod 15k+
P: 31,494
I think this one's a little more complicated than it first appears.
This should work.
Twins mean a simple count of records matching the '5 or 7' criteria is not good enough.
I'm afraid it does have two levels of subqueries but, hey, what's a subquery or two between friends ;-).

Expand|Select|Wrap|Line Numbers
  1. SELECT tblParent.Family
  2. FROM tblParent INNER JOIN (SELECT Family, Count(Grade) AS 5And7
  3. FROM (SELECT DISTINCT Family, Grade
  4. FROM tblStudent
  5. WHERE (Grade In (5,7))
  6. GROUP BY Family, Grade) AS subStudent
  7. GROUP BY Family) AS subStudent ON tblParent.Family = subStudent.Family
  8. WHERE (((subStudent.[5And7])=2));
Oct 24 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Not bad

Down to interpretation again. I took the question to be if any child in grade rather than count of children in grade.


I think this one's a little more complicated than it first appears.
This should work.
Twins mean a simple count of records matching the '5 or 7' criteria is not good enough.
I'm afraid it does have two levels of subqueries but, hey, what's a subquery or two between friends ;-).

Expand|Select|Wrap|Line Numbers
  1. SELECT tblParent.Family
  2. FROM tblParent INNER JOIN (SELECT Family, Count(Grade) AS 5And7
  3. FROM (SELECT DISTINCT Family, Grade
  4. FROM tblStudent
  5. WHERE (Grade In (5,7))
  6. GROUP BY Family, Grade) AS subStudent
  7. GROUP BY Family) AS subStudent ON tblParent.Family = subStudent.Family
  8. WHERE (((subStudent.[5And7])=2));
Oct 24 '06 #5

NeoPa
Expert Mod 15k+
P: 31,494
MMcCarthy,

Thanks for the grats - I've seen a lot of your work so that means a lot.
As I said though, this isn't straightforward. The count is after grouping then filtering so it really means :-

If the total number of different grades found in ( the set of children containing either 5 or 7 ) is equal to 2.

So, for instance, if a family had 2 children (twins), both in grade 5, they would only count as 1 and would therefore not get selected.

It's always a lot easier to formulate complicated code than it is to interpret it I find and that lot was more concise than explicit.

I'm assuming of course, that the code is correct and wasn't lost in translation from my brain to the paper. That wouldn't be a first.
Oct 24 '06 #6

Post your reply

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