Re: SQL LEFT JOIN and WHERE - Mailing list pgsql-novice

Rai Developer wrote:
> 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.

I would think that every cage has a valid id - meaning the id>0 should
not be needed.

> 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

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 c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE
date='2008-02-15')

ORDER BY order_position


The main problem is the where clause from above got merged into the left
join clause.

That would give you the cages with a reservation on the date.
Change the "AND c.id IN (SELECT..." to "AND c.id NOT IN (SELECT..." to
get the ones without a reservation.


I changed the AND r.id to AND c.id - I feel certain you want the cage id
not the id of the reservation entry matched against the reserved_cage_id
from reserved_days.


>
>
> 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 !

reserved_cages would seem to be unnecessary as you can get the list of
reserved cage id's from the SELECT -

SELECT c.id
FROM cages c

WHERE ( c.cages_type_id=1 AND  c.id > 0)
AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE
date='2008-02-15')

ORDER BY order_position


If you are using the reserved_cages to hold data for who has reserved
it, I would think it be better stored in the reserved_days table. (at
least the id of the customer reserving the cage)



--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz