SQL - Mixing ANDs and ORs
I'm not a SQL Expert. I'm using SQL Server 2005 and I'm trying to figure out how to structure a query so that it can meet several requirements. I have two tables def开发者_StackOverflow中文版ined as follows:
Classroom
- ID
- DepartmentID
- TeacherName
- RoomNumber
Student
- ID
- Name
- ClassroomID
I'm trying to build a query that says, "give me the classrooms in department [x] OR department [y] that have more than 30 students AND give me the classrooms in department [w] OR department [z] that have more than 40 students. I'm confused on how to mix the ANDs and the ORs properly in my SQL. Currently, I am trying the following:
SELECT
c.RoomNumber,
c.TeacherName
FROM
Classroom c
WHERE
c.[DepartmentID]=5 AND (SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID > 30) OR
c.[DepartmentID]=6 AND (SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID > 30) OR
c.[DepartmentID]=7 AND (SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID > 40) OR
c.[DepartmentID]=8 AND (SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID > 40)
What am I doing wrong? Thank you!
SELECT c.id
FROM Classroom c
JOIN Students s
ON s.Classroom = c.Id
WHERE DepartmentID IN ('X', 'Y', 'W', 'Z')
GROUP BY
c.id, c.DepartmentID
HAVING COUNT(*) >= CASE WHEN DepartmentID IN ('X', 'Y') THEN 30 ELSE 40 END
You need to add a set of parens around the first two boolean expressions that are joined by the AND
should be
(...) OR
(...)
etc
Try this
(SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID) >30
instead of this
(SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID > 30)
;]
Be very explicit with your parens.
Notice that I added 3 more sets of parens.
((c.[DepartmentID]=5) AND ((SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID) > 30)) OR
精彩评论