Re: SQL LEFT JOIN and WHERE

Hello,

On 16/02/2008, at 14:23, Shane Ambler wrote:

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

yes, I need it, because there are special cages that have a negative id

>> 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 need to display ALL the cages, no matter if they have a reservation
or not on the passed day

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

well, the id from IN (SELECT reserved_cage_id FROM reserved_days WHERE
date='2008-02-15') is the id of the reserved_cages, I can't change
into this AND r.id to AND c.id becaue they are different id values.

basically, what I want is display the name of the customer who has the
reservation on the current cage and in the passed period. in the
reserved_cage I have the date_in and date_out of the reservation, and
in the reserved_days I have one row for each day the reservations
occur, with other data that I need.

maybe I can change the SELECT for using only two tables, using ....
WHERE (reserved_cage.date_in <= '2008-02-15') AND
(reserved_cage.date_out >= '2008-02-15')

maybe this also works, and I use only two tables:

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.date_in <= '2008-02-15') AND (r.date_out >= '2008-02-15') ORDER BY
order_position

but again, I think the WHERE clausule is affecting the LEFT JOIN ....
and I'm only getting the cages with some reservation ...

any idea on how to change it for showing all the cages, no matter if
they have reservation or not ?

thanks again,

raimon

>
>> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>