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