Select a group only if each item meets the criteria
I have a simple database schema: Child one----many SchoolPreferences
The children table consists of:
ChildId, Name, Surname, Age
SchoolPreferences consists of:
PreferenceId, ChildId, PreferenceNumber, SchoolName
This is for re-teaching at school. Each child can choose a maximum of 3 schools where they want to go. If they are accepted in PreferenceNumber 1, then they are that school, if they do not meet the conditions they receive in PreferenceNumber 2, and if they fail there, then their last chance is in the third school of preference.
I need to select schools that were only selected as PreferenceNumber 1. Therefore, if any child selected a school as PreferenceNumber 2 or 3, the school should not appear at all as a result.
For now, I only know how to select individual rows, but I need to filter the groups, so each school is a group, and if any of the settings> 1, then this whole group shouldn't appear in the result.
So far I have this, but as I said, this only works for single lines, not schools:
select SchoolPreferences.PreferenceId,
Children.name,
Children.Surname
from Children
inner join SchoolPreferences on Children.ChildId = SchoolPreferences.ChildId
group by SchoolPreferences.PreferenceId,
Children.name,
Children.Surname
having (((SchoolPreferences.PreferenceNumber) = 1));
source to share
The anti-join pattern will do the trick. No children's table needed. It looks like the only identifier we have for the school is schoolname
.
If I understand the spec, return a list of schools ( schoolname
) that appeared ONLY as preferred number 1 and did not appear in any other preferred number ...
Something like that:
SELECT p1.schoolname
FROM SchoolPreferences p1
LEFT
JOIN SchoolPreferences pn
ON pn.schoolname = p1.schoolname
AND pn.preferencenumber <> 1
WHERE pn.schoolname IS NULL
AND p1.preferencenumber = 1
GROUP
BY p1.schoolname
There are other query patterns that return an equivalent result, eg.
SELECT p1.schoolname
FROM SchoolPreferences p1
WHERE p1.preferencenumber = 1
AND NOT EXISTS
( SELECT 1
FROM SchoolPreferences pn
WHERE pn.schoolname = p1.schoolname
AND pn.preferencenumber <> 1
)
GROUP
BY p1.schoolname
If we want to include children who had school as preferred number 1, we remove the GROUP BY and we can make a join to the table Children
SELECT p1.schoolname
, p1.preferenceid
, p1.childid
, ch.name
, ch.surname
FROM SchoolPreferences p1
JOIN Children ch
ON ch.childid = p1.childid
LEFT
JOIN SchoolPreferences pn
ON pn.schoolname = p1.schoolname
AND pn.preferencenumber <> 1
WHERE pn.schoolname IS NULL
AND p1.preferencenumber = 1
ORDER
BY p1.schoolname
, p1.childid
source to share