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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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