Re: SQL LEFT JOIN and WHERE - Mailing list pgsql-novice
From | Rai Developer |
---|---|
Subject | Re: SQL LEFT JOIN and WHERE |
Date | |
Msg-id | 09C3F0AB-1718-4486-A0AC-52AE83D16635@montx.com Whole thread Raw |
In response to | Re: SQL LEFT JOIN and WHERE (Rai Developer <coder@montx.com>) |
List | pgsql-novice |
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@postgresql.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 >
pgsql-novice by date: