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

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

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
>