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

Re: SQL LEFT JOIN and WHERE

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
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-17 00:01:21
Message-ID: 20080216155519.I5562@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Sat, 16 Feb 2008, 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

You should be able to put what you have as the temp table definition query
as a subselect in join probably. So something like the following:

SELECT c.*,r.* FROM cages c LEFT JOIN (SELECT * from reserved_cages r
WHERE r.date_in <= '2008-02-15' AND r.date_out >= '2008-02-15') r ON
(c.id=r.cage_id) WHERE (c.cages_type_id=1 AND c.id>0) ORDER BY
order_position;


In response to

pgsql-novice by date

Next:From: Brent AustinDate: 2008-02-17 01:07:25
Subject: configuring psqlodbc-08.03.0100
Previous:From: Shane AmblerDate: 2008-02-16 21:17:06
Subject: Re: SQL LEFT JOIN and WHERE

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