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

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

Something like:

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

Am sure you can do the rest.

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

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

TQ SO MUCH!

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

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.

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.