Benjamin Smith wrote:
> Now, I want to get a result like:
>
> classroom | students | seats
> 101A 0 25
> 101B 22 30
> 102A 11 0
> ... etc.
>
> Something somewhat akin to
>
> select classroom.title,
> count(students.id) AS students,
> count(seats.id) AS seats
> from classrooms, students, seats
> where classrooms.id=students.classrooms_id
> and classrooms.id=seats.id
try
select classroom.title,
(select count(*) from students
where classrooms_id = c.id) AS students,
(select count(*) from seats
where classrooms_id = c.id) AS students,
count(seats.id) AS seats
from classrooms c
Cheers,
Eze