0

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}.

Edited by nizam27391

2
Contributors
4
Replies
17
Views
3 Years
Discussion Span
Last Post by pritaeas
0

Something like:

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

Am sure you can do the rest.

0

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.

0

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 ()

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.