Re: [GENERAL] Return LEFT JOINed tables when one has no matching column

2009-06-24 Thread Madison Kelly 

Harald Fuchs wrote:

In article <4a425379.90...@alteeve.com>,
Madison Kelly  writes:


SELECT
a.tbl1_name,
b.tbl2_date,
c.tbl3_value AS some_value
FROM
table_1 a
LEFT JOIN
table_2 b ON (a.tbl1_id=b.tbl2_tbl1_id)
LEFT JOIN
table_3 c ON (a.tbl1_id=c.tbl3_tbl1_id)
WHERE
c.tbl3_variable='some_variable'
AND
a.tbl1_id=123;



  I want the data from table_1 and table_2 to return and table_3 to
return NULL when there is no matching
c.tbl3_variable='some_variable'. Is this possible?


Move "c.tbl3_variable='some_variable'" from WHERE to c's ON clause.


Bingo, thank you!

Madi

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Return LEFT JOINed tables when one has no matching column

2009-06-24 Thread Harald Fuchs 
In article <4a425379.90...@alteeve.com>,
Madison Kelly  writes:

> SELECT
>   a.tbl1_name,
>   b.tbl2_date,
>   c.tbl3_value AS some_value
> FROM
>   table_1 a
> LEFT JOIN
>   table_2 b ON (a.tbl1_id=b.tbl2_tbl1_id)
> LEFT JOIN
>   table_3 c ON (a.tbl1_id=c.tbl3_tbl1_id)
> WHERE
>   c.tbl3_variable='some_variable'
> AND
>   a.tbl1_id=123;

>   I want the data from table_1 and table_2 to return and table_3 to
> return NULL when there is no matching
> c.tbl3_variable='some_variable'. Is this possible?

Move "c.tbl3_variable='some_variable'" from WHERE to c's ON clause.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Return LEFT JOINed tables when one has no matching column

2009-06-24 Thread Madison Kelly 

Hi all,

  I've got a variation on a question I asked some time ago... I've got 
a table that is simply a collection of "variable" -> "value" columns 
with a pointer to another table. I use this as little as possible, given 
how much of a headache it is, but I've run into a situation where I need 
to pull something from it in a JOIN query. Trick is, the column 
'variable' may simply not exist, but I want the rest of the query to 
return and let it be NULL.


  Specifically, I've got a query like this:

SELECT
a.tbl1_name,
b.tbl2_date,
c.tbl3_value AS some_value
FROM
table_1 a
LEFT JOIN
table_2 b ON (a.tbl1_id=b.tbl2_tbl1_id)
LEFT JOIN
table_3 c ON (a.tbl1_id=c.tbl3_tbl1_id)
WHERE
c.tbl3_variable='some_variable'
AND
a.tbl1_id=123;

  I want the data from table_1 and table_2 to return and table_3 to 
return NULL when there is no matching c.tbl3_variable='some_variable'. 
Is this possible?


Thanks as always!

Madi

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general