SQL structure

[vlitim]

I have two tables, the first one a list of pupils and the second a choices table where you can insert there course choices in.

pupils:
id
name

choices:
id
pupilID

I need to be able to list all of the students who haven't got 8 entries in the choices table using a stored procedur, and I cannot get it to work for anything!

Please Help!

Cheers
Tim

 

[tlbroadbent]

This should show pupils with fewer than 8 entries in the Choices table.

Select Id, Name
From Pupils
Where If Not In
(Select PupilId
From Choices
Group By PupilID
Having count(ID)>=8) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[stsuing]

Alternative:

SELECT name, id
FROM (SELECT COUNT(Choices.Pupilid) AS cnt_id, Choices.id,
Pupils.name
FROM Choices INNER JOIN
Pupils ON Choices.id = Pupils.id
GROUP BY Choices.id, Pupils.name) AS dts
WHERE cnt_id < 8