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

Re: SQL LEFT JOIN and WHERE

From: Shane Ambler <pgsql(at)Sheeky(dot)Biz>
To: Rai Developer <coder(at)montx(dot)com>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: SQL LEFT JOIN and WHERE
Date: 2008-02-16 13:23:20
Message-ID: 47B6E3C8.1090801@Sheeky.Biz (view raw or flat)
Thread:
Lists: pgsql-novice
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.

> 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 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.


> 
> 
> 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

In response to

Responses

pgsql-novice by date

Next:From: Rai DeveloperDate: 2008-02-16 14:41:49
Subject: Re: SQL LEFT JOIN and WHERE
Previous:From: Rai DeveloperDate: 2008-02-16 08:21:03
Subject: SQL LEFT JOIN and WHERE

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