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 19:53:09
Message-ID: 48960F4A-B26D-47AD-B740-298C0EDDC292@montx.com (view raw or flat)
Thread:
Lists: 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
>



In response to

Responses

pgsql-novice by date

Next:From: Andrej Ricnik-BayDate: 2008-02-16 19:55:43
Subject: Re: pgsql-novice@postgresql.org
Previous:From: Ubence QuevedoDate: 2008-02-16 15:39:15
Subject: pgsql-novice@postgresql.org

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