Skip site navigation (1) Skip section navigation (2)

Re: SQL LEFT JOIN and WHERE

From: Rai Developer <coder(at)montx(dot)com>
To: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: SQL LEFT JOIN and WHERE
Date: 2008-02-16 20:39:05
Message-ID: 0416C5DC-5EB0-4CCD-8962-F8F6DBC300DE@montx.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
>



In response to

Responses

pgsql-novice by date

Next:From: Shane AmblerDate: 2008-02-16 21:17:06
Subject: Re: SQL LEFT JOIN and WHERE
Previous:From: Andrej Ricnik-BayDate: 2008-02-16 19:55:43
Subject: Re: pgsql-novice@postgresql.org

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group