How do I select the classroom from one table based on count of another tabl

[nizam27391]

Hi.I'm Nizam.
Sorry if the question I asked is unclear.

I have the table as below

Classroom
- id
- capacity
- code
- ......

Schedule
- id
- group_id
- classroom_id
- ......

The schedule will be filled with the group and classroom.

My problem now is how can I retrieve the classrooms from table Classroom that is based on the occurence of classroom_id in Schedule table.

Let say.. we have classroom {1,2,3,4,5,6,7} ..
the occurence of classroom_id = 2 in Schedule is 8 times.
And I want to select classroom from Classroom table that contains less than 8 occurence in Schedule table which will only give result {1,3,4,5,6,7}.

[pritaeas]

Something like:

SELECT * FROM Schedule GROUP BY classroom_id HAVING COUNT(*) < 8

Am sure you can do the rest.

[nizam27391]

THANKS!! I THINK I CAN FIGURE IT OUt NOW..

TQ SO MUCH!

[nizam27391]

Hi Pritaeas..
I just realized that the code only return whats on the Schedule table..
How can I return it from the Classroom table?

For example :
in Classroom, I have data = {1,2,3,4,5,6,7,8,9}.
in Schedule, I have sc = {2,2,4,5,1,5,7}

And I want to find the classroom that is not exist in Schedule or only exist less than two.

That should return {1,3,4,6,7,8,9}.

Sorry I just realized it after getting dream about it.

[pritaeas]

Use my query above to select all rooms in schedule where there are at least two, then use that result as a sub-query for Classroom using WHERE id NOT IN ()