Re: SQL LEFT JOIN and WHERE - Mailing list pgsql-novice
From | Rai Developer |
---|---|
Subject | Re: SQL LEFT JOIN and WHERE |
Date | |
Msg-id | 0416C5DC-5EB0-4CCD-8962-F8F6DBC300DE@montx.com Whole thread Raw |
In response to | Re: SQL LEFT JOIN and WHERE (Rai Developer <coder@montx.com>) |
Responses |
Re: SQL LEFT JOIN and WHERE
(Rai Developer <coder@montx.com>)
|
List | 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@postgresql.org so that your > message can get through to the mailing list cleanly >
pgsql-novice by date: