Re: SQL LEFT JOIN and WHERE

Shane and Stephan,

thanks for your ideas, I'be veen very busy but I'm going to try them
as soon as possible ...

regards,

raimon

On 16/02/2008, at 21:39, Rai Developer wrote:

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