SQL LEFT JOIN and WHERE

Hello,


I'm struggling my brain for some days without success ...

I have three tables:

cages
reserved_cages
reserved_days


Inside cages, I want to display all the id > 0 and animal_type_id=1,
and I want to display all of them no matter if it has some
reserved_cages related, so I have to use a LEFT JOIN.

Ok, now, the reserved_cages must exist only when there are rows in the
reserved_days table.

What I'm getting, sometimes, is only the cages that has some
reserved_cages (because they have some reserved_days), and when I try
to display all of the cages, I can't exclude the ones that have id>0
or animal_type_id=1, I get all of them, so it seems the WHERE clausule
is not working ...

here are the code:


here is the initial cages that I want to display, no matter if they
have related data or not:

SELECT c.id, c.name FROM cages c WHERE ( c.cages_type_id=1 AND  c.id >
0) ORDER BY order_position


this seems to work, but I get ALL the cages, no matter if they are
cages_type_id<>1  (I only want to display=1)

SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
(c.id=r.cage_id) AND ( c.cages_type_id=1 AND  c.id > 0) AND r.id IN
(SELECT reserved_cage_id FROM reserved_days WHERE date='2008-02-15')
ORDER BY order_position


if I change the first AND for a WHERE, like this:

SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
(c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND  c.id > 0) AND r.id IN
(SELECT reserved_cage_id FROM reserved_days WHERE date='2008-02-15')
ORDER BY order_position

I get only the cages that has some reservations on the date performed.


The relations between tables are:

cages: id
reserved_cages: cage_id
reserved_days: reserved_cage_id

So I have to query for a given day if there are reservations, pass
those rows to the reserved_cages (where I only store the date_in and
date_out).

I think I can use an extra field in the reserved_days adding a
cage_id, the SELECT would be much simpler and I think much faster, but
I'm trying to avoid duplicated data, and at the same time, learning
postgresql and try to find more or less the limitations, maybe those
limitations (if they're limitations) come from my head or from sql ...

as always, thanks for your help !


regards,


raimon