General SQL question

Dev Shed Forums Sponsor:

August 1st, 2012, 03:08 PM

argo4242

Registered User

Join Date: Aug 2012

Posts: 6

Time spent in forums: 54 m 37 sec
Reputation Power: 0

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

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 2,047

Time spent in forums: 1 Month 3 Weeks 5 h 5 m 45 sec
Reputation Power: 812

Hi,

Join both tables, group by the student ID and then select those students with exactly one row and the right class type.

Code:

SELECT
	student.id,
	student.name
FROM
	student
	INNER JOIN classes ON (student.id = classes.student_fk)
GROUP BY student.id
HAVING COUNT(*) = 1 AND EVERY(classes.class_type = ...);

Instead of "EVERY", you could also use "BOOL_OR". I don't know which one is faster in this case.

August 2nd, 2012, 07:52 AM

argo4242

Registered User

Join Date: Aug 2012

Posts: 6

Time spent in forums: 54 m 37 sec
Reputation Power: 0

Thanks!

Quote:

Originally Posted by Jacques1

Hi,

Join both tables, group by the student ID and then select those students with exactly one row and the right class type.

Code:

SELECT
	student.id,
	student.name
FROM
	student
	INNER JOIN classes ON (student.id = classes.student_fk)
GROUP BY student.id
HAVING COUNT(*) = 1 AND EVERY(classes.class_type = ...);

Instead of "EVERY", you could also use "BOOL_OR". I don't know which one is faster in this case.

Thanks for the speedy reply! If this works as expected I will respond back here to confirm.