Re: SQL LEFT JOIN and WHERE

On 16/02/2008, at 20:53, Rai Developer wrote:

> Sorry for replying on top ...
>
> I can do it like this ...
>
> CREATE TEMP TABLE d_reserved_cages AS SELECT * FROM reserved_cages r
> WHERE (r.date_in <= '2008-02-15' AND r.date_out >= '2008-02-15') ;
>
> SELECT c.*, r.* FROM cages c LEFT JOIN d_reserved_cages r ON
> (c.id=r.cage_id) AND ( c.cages_type_id=1 AND c.id > 0) ORDER BY
> order_position

There was a mistake, the first AND must be WHERE ...

SELECT c.*, r.* FROM cages c LEFT JOIN d_reserved_cages r ON
(c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND c.id > 0) ORDER BY
order_position

r.

> DROP TABLE d_reserved_cages;
>
> but I would prefer a direct solution without using/creating extra
> tables, I think it shouldn't be too complicated ... but at least it
> is for me ...
>
> thanks,
>
>
> raimon
>
>
> On 16/02/2008, at 15:41, Rai Developer wrote:
>
>> 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
>>>
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>