August 1st, 2012, 03:08 PM
General SQL question
I have a pretty basic problem, but not too sql savy. I will keep it simple with the example vs using my real tables.
Imagine a table 'student' with columns 'id', 'name'.
Also a table 'classes' with columns 'id', 'student_fk', 'class_type'
student_fk is hopefully clearly the foreign key to the student.id primary key. I know this is not great design, but for the sake of this example it will do.
What i am looking for is a query to select only those students that have one class. Also that one class needs to be of a certain class_type.
The idea is that the student may have many classes of different types and i only want to find student that have ONLY 1 class associated with them of that type. If the student has a class of that type, but also has another class of a different type i don't want that student returned.
Hopefully this description makes sense. Any help is appreciated. Thanks in advance.
August 1st, 2012, 04:41 PM
Join both tables, group by the student ID and then select those students with exactly one row and the right class type.
Instead of "EVERY", you could also use "BOOL_OR". I don't know which one is faster in this case.
INNER JOIN classes ON (student.id = classes.student_fk)
GROUP BY student.id
HAVING COUNT(*) = 1 AND EVERY(classes.class_type = ...);
August 2nd, 2012, 07:52 AM
Originally Posted by Jacques1
Thanks for the speedy reply! If this works as expected I will respond back here to confirm.
August 23rd, 2012, 01:03 PM
It worked like a champ. thanks.
Originally Posted by argo4242