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