Conditionally set column values in SQL result
I'm creating a result set based on a join of multiple tables. For the sake of simplicity, I have attached an image showing my theoretical layout:
Diagram http://www.liquidarts.net/images/stackoverflow/sql-theo.png
My result set normally looks like:
table1.value, table2.value, table3.value
However, if table3.table4_key is set, I would want the result set to instead be:
table1.value, table2.value, table4.value
Any suggestions would be greatly appreciated. I'm trying to do all of my data organization purely in SQL, and avoid doing any procedural data smashing in PHP.
Solution SQL, as per answer开发者_StackOverflow below:
select
table1.value,
table2.value,
COALESCE(table4.value, table3.value)
from table1 join table2 on
table2.key = table1.key join table3 on
table3.key = table2.key left join table4 on
table4.key = table3.table4_key
Assuming you LEFT JOIN
table4 on table3.table4_key you can use COALESCE
to achieve the desired result.
SELECT table1.value, table2.value, COALESCE(table4.value, table3.value)
"Returns the first non-NULL value in the list, or NULL if there are no non-NULL values."
精彩评论