I am not really a SQL Expert. I am using SQL Server 2005 and I am trying to puzzle out how you can structure a question to ensure that it may meet several needs. I've two tables defined the following:

Classroom
 - ID
 - DepartmentID
 - TeacherName
 - RoomNumber

Student
 - ID
 - Name
 - ClassroomID

I am attempting to develop a query that states, "produce the classes in department [x] OR department [y] which have a lot more than 30 students And provide me the classes in department [w] OR department [z] which have a lot more than 40 students. I am confused regarding how to mix the ANDs and also the ORs correctly during my SQL. Presently, I'm using 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 shall we be held doing wrong? Thanks!

You have to add some parens round the first couple of boolean expressions which are became a member of through the AND

ought to be

(...) OR
(...) 

etc

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

Do this

(SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID) >30

rather than this

(SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID > 30)

]

Be very explicit together with your parens.

Observe that I added 3 more teams of parens.

((c.[DepartmentID]=5) AND ((SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID) > 30)) OR