CODEWITHSUNDEEP
sqldb2
Ardia
Ardia

Reputation: 89

Multiple Selects (with Null) from the same table

So I have to write a SQL in DB2, and I cant figure out how to do it. Something like pick these field codes and their values from the Finance Table as long as they are above $10,000

Select 
(A.Value),
(B.Value),
(C.Value)

...

From 
Client  K,
Finance A,
Finance B,
Finance C
...

The problem is in the where statement. I cannot put:

Where
K.Client  = A.Client
AND A.FieldCode = 1
AND A.Value > 10000
AND K.Client  = B.Client
AND B.FieldCode = 2
AND B.Value > 10000
...

and so on... because that doesnt include Nulls, so it drastically reduces the Result Set, the more times I call the Finance table.

How do I keep the above formatting and get it to include nulls so that it will display the line as long as either Finance A OR Finance B OR Finance C etc exists?

(Note: doing the first most obvious thing like repeatedly calling the Finance table once, say Finance A, but no B,C,D etc does not work for this problem because the results (from A,B,C,D etc) cannot be spaced out over many lines).

This is a compressed version of what I am doing:

SELECT 
  A.CLIENT_ID,
  A.PERIOD_ID,
  FN0.AMOUNT,
  FN2.AMOUNT
FROM ASSESMENT A
LEFT OUTER JOIN FINANCE FN0
     ON A.CLIENT_ID = FN0.CLIENT_ID
    AND A.PERIOD_ID = FN0.PERIOD_ID
LEFT OUTER JOIN FINANCE FN1
     ON A.CLIENT_ID = FN1.CLIENT_ID
    AND A.PERIOD_ID = FN1.PERIOD_ID
WHERE
  FN0.FLD_CD  = 1258860
 AND FN1.FLD_CD = 1258861

The problem Im still having is, if I blank out the FN1 related lines, I get a lot more returned values. AKA it is still not including NULL values, and only returning values if ALL field codes have values.

Upvotes: 2

Views: 93

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

You need Left Outer Join instead of Comma separated INNER Join 

SELECT A.Value,
       B.Value,
       C.Value
FROM   Client K
       LEFT JOIN Finance A
              ON K.Client = A.Client
                 AND A.Value > 10000
                 AND A.FieldCode = 1
       LEFT JOIN Finance B
              ON K.Client = B.Client
                 AND B.Value > 10000
                 AND B.FieldCode = 2
       LEFT JOIN Finance C
              ON K.Client = c.Client 
                 ......

Update :

Move the Left table filters to ON condition else it will be implicitly converted to INNER JOIN. Try like this

SELECT A.client_id, 
       A.period_id, 
       FN0.amount, 
       fn2.amount 
FROM   assesment A 
       LEFT OUTER JOIN finance FN0 
                    ON A.client_id = FN0.client_id 
                       AND A.period_id = FN0.period_id 
                       AND FN0.fld_cd = 1258860 
       LEFT OUTER JOIN finance FN1 
                    ON A.client_id = FN1.client_id 
                       AND A.period_id = FN1.period_id 
                       AND FN1.fld_cd = 1258861

Upvotes: 3

Related Questions