Thread: Return LEFT JOINed tables when one has no matching column

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

In article <4A425379.90907@alteeve.com>,
Madison Kelly <linux@alteeve.com> 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.

Harald Fuchs wrote:
> In article <4A425379.90907@alteeve.com>,
> Madison Kelly <linux@alteeve.com> 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