Dealing with NULL in MySQL Query

[lostcommander]

Short Version: Is there some way to get a single integer value out of a set of columns when it is known that all but 1 will be null? I have tried simply adding them with +, and SUM, MAX, MIN, etc. all only work with GROUP BY stuff. e.g.
(tableB.valueX + tableC.valueY) AS value
MAX(tableB.valueX, tableC.valueY) AS value
...
Is there a way to select out the non-null value or to combine away the NULL values?


Long Story: I have 3 tables. Table A is the primary table and has an ID. Tables B and C contain additional information about rows of table A and each B or C row has an ID matching it to a row in table A. Every row in table A matches exactly 1 row in either table B or table C, never both. Tables B and C share a column which I would like to have SELECT-ed out under a single column (but they do not share with table D, E... so moving this column to table A is not a solution). Currently I am simply Left JOIN-ing everything and then finding the existing value on the PHP side of things, but it is sort of ugly and will be hard to maintain if/as more such info tables are added.

 

[descalzo]

SELECT SUM/MAX/MIN as value from A inner join B on A.id = B.foreignKey
UNION
SELECT SUM/MAX/MIN  as value from A inner join C on A.id = C.foreignKey

Sould return two rows. Add or take max/min

 

[lostcommander]

Yup, that gets is. I had not remembered/thought of using INNER JOIN twice, plus the UNION. Makes for a horribly ugly query, but at least it keeps the complication all in one place. Thanks!