SQL Join / Union

Question

I have two statements that I want to merge into one output.

Statement One:

select name from auxiliary_variable_inquiry
where inquiry_idbr_code = '063' 

Returns the following list of names:

Name
------------
Affiliates
NetBookValue
Parents
Worldbase

Statement Two:

select name, value from auxiliary_variable_value
where inquiry_idbr_code = '063'
and ru_ref = 20120000008
and period = 200912

Returns the following:

Name        Value
-------------------
Affiliates      112
NetBookValue    225.700

I would like to have an output like this:

Name         Value
-------------------
Affiliates    112 
NetBookValue  225.700
Parents       0
Worldbase     0

So basically, if the second query only returns 2 names and values, I'd still like to display the complete set of names from the first query, with no values. If all four values were returned by both queries, then all four would be displayed.

Sorry I must add, im using Ingres SQL so im unable to use the ISNULL function.

Answer 1

I'd recommend a self-JOIN using the LEFT OUTER JOIN syntax. Include your 'extra' conditions from the second query in the JOIN condition, while the first conditions stay in the WHERE, like this:

    select a.name, CASE WHEN b.Value IS NULL THEN 0 ELSE b.Value END AS Value
    from 
        auxiliary_variable_inquiry a
         LEFT JOIN 
        auxiliary_variable_inquiry b ON 
            a.name = b.name and -- replace this with your real ID-based JOIN 
            a.inquiry_idbr_code = b.inquiry_idbr_code AND
            b.ru_ref = 20120000008 AND
            b.period = 200912
    where a.inquiry_idbr_code = '063'