SQL: Querying within multiple records that represent the same individuals

Question

I've been given an Access database of university alumni. It contains details of the schools each student has been to, and in particular whether these schools were state or fee-paying schools. Here is a simplified version of the structure of the relevant table:

StudentID   AgeStart    AgeFinish   State_Fee
-----------------------------------------------------------
23          5           10          state
23          10          18          state
24          6           9           state
24          9           12          fee
24          12          18          fee

The primary key is StudentID+AgeStart, so records for the same students are stored across multiple rows e.g. student 23 above attended two schools, both state schools. Student 24 attended three schools, one of them state and two fee-paying.

I need to ask the question: How many people spent their entire schooling in private/paid education? Is it possible to select these people in SQL? I'm struggling because students are from around the world and start and end schools at different ages. I need to be able to say 'Select StudentIDs where State_paid=2 for all occurences of this ID'. Anyone any ideas?

score 1 · Accepted Answer · answered Nov 03 '13 at 22:41

1

This will find all students who never were in a state school.

SELECT DISTINCT StudentID FROM alumni
WHERE StudentID NOT IN (
    SELECT StudentID FROM alumni WHERE State_Fee = 'state'
)

answered Nov 03 '13 at 22:41

Paul Draper

78,542
46
206
285

Thanks so much! That's fantastic. I'd been rattling my head for ages with it. – MartinC Nov 03 '13 at 22:56
@user2950853, for future reference, also read about `EXISTS` (very similar). – Paul Draper Nov 03 '13 at 23:08

score 1 · Answer 2 · answered Nov 03 '13 at 23:40

An alternative approach to answering this question is to use aggregation with a having clause. I prefer this approach because it is more general for these types of queries. (This is an example of a set-within-sets query.)

select StudentId
from t
group by StudentId
having sum(iif(State_Fee = "state", 1, 0) = 0;

What the having clause is doing is counting the number of times that "state" occurs for each StudentId. A StudentId passes the test when there are no such records.

SQL: Querying within multiple records that represent the same individuals

2 Answers2