Обсуждение: Dumb question about count()

I'm sure I've done this before, but for some reason, my main noodle is drawing
a blank. Assume we have three tables defined thusly:

create table classrooms (
id serial unique not null,
name varchar
);

create table seats (
classrooms_id integer not null references classrooms(id),
position varchar
);

create table students(
classrooms_id integer not null references classrooms(id),
name varchar
);

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

Except that it counts 0s for seats/students.

Why can't I recall/find how to do this particular join?

-Ben
--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978

On Thu, Jul 21, 2005 at 10:09:07PM -0700, Benjamin Smith wrote:
> 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
>
> Except that it counts 0s for seats/students.
>
> Why can't I recall/find how to do this particular join?

Sounds like you're looking for an outer join.

http://www.postgresql.org/docs/8.0/static/tutorial-join.html
http://www.postgresql.org/docs/8.0/static/queries-table-expressions.html#QUERIES-FROM

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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