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.

What version of Ingres are you using? IFNULL() is available or you can used CASE as someone did below. The $ is probably because the results are being returned as MONEY type. What's the datatype of Value? — PaulM, Nov 04 '14 at 13:47

score 2 · Answer 1 · answered Nov 04 '14 at 12:44

2

You can do a left join. This ensures that all records from the first table will stay included. Where value is null, no child record was found, and we use coalesce to display 0 in these cases.

select i.name, COALESCE(v.Value,0) from auxiliary_variable_inquiry i
left join auxiliary_variable_value v
on v.inquiry_idbr_code = i.inquiry_idbr_code
and v.ru_ref = 20120000008
and v.period = 200912
where i.inquiry_idbr_code = '063'

answered Nov 04 '14 at 12:44

wvdz

16,251
4
53
90

Hi i've tried this, for some reason its added a $ on the col2 and the values dont seem correct. rather than just filling in 0 for parents and worldbase its copying in values from affiliates and NBV – Jon Lavercombe Nov 04 '14 at 12:47
That's strange. If you can reproduce this problem on http://sqlfiddle.com/ I'll gladly take a look at it. – wvdz Nov 04 '14 at 12:58
@popovitsj, I think this is because you link on the `inquiry_idbr_code` value, which is `063` for all four records in the example. The JOIN needs to include the `name` field (see my answer below - maybe you'll be able to figure out the $ thing), or an ID that is unique to each record. – AHiggins Nov 04 '14 at 13:13

AHiggins · Accepted Answer · 2014-11-04T13:09:45.403

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'

edited Nov 04 '14 at 13:09

answered Nov 04 '14 at 12:47

AHiggins

7,029
6
36
54

Swapped out COALESCE() for ISNULL() – AHiggins Nov 04 '14 at 13:01
this is great but once again for some reason its add $ to the value column?? – Jon Lavercombe Nov 04 '14 at 13:03
Do you mean that the name of the column contains the $ sign (e.g., `$Value`, or that the contents are including it (e.g., `$0`)? – AHiggins Nov 04 '14 at 13:05
Hi, that the contents have $ included yeah? – Jon Lavercombe Nov 04 '14 at 13:06
OK, new edit: no COALESCE or ISNULL, just a simple CASE statement. Does this show up okay? – AHiggins Nov 04 '14 at 13:10

score 0 · Answer 3 · edited Nov 04 '14 at 12:48

0

if i got right, you should use something like:

SELECT i.NAME,
       v.NAME,
       v.value
FROM   auxiliary_variable_inquiry i
       LEFT JOIN auxiliary_variable_value v
              ON i.inquiry_idbr_code = v.inquiry_idbr_code
WHERE  v.ru_ref = 20120000008
       AND v.period = 200912

edited Nov 04 '14 at 12:48

Pரதீப்

91,748
19
131
172

answered Nov 04 '14 at 12:47

Nico

21
2

Nico, using the elements from your second table in the `WHERE` clause effectively turns the `LEFT JOIN` into an `INNER JOIN` ... – AHiggins Nov 04 '14 at 12:51
Hi this doesnt quite return what im after. The first name column seems to be duplicated but there are no 0 values at all just duplicates of the returned ones. – Jon Lavercombe Nov 04 '14 at 12:54

SQL Join / Union

3 Answers3

Linked