Re: SQL LEFT JOIN and WHERE

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;