case when subquery multiple row

Question

I have tree table on DB2 9.7.01

Table A (here are start data)

codeA |type
-----------------------------
P003 |P
K001 |K

Table B (here are product data)

codeB |description
-----------------------------
P001 |Product one
P002 |Product two
P003 |Product three

Table C (here are kit data; kit include one or more product)

codeC |description |codeB
-----------------------------
K001 |Kit one      |P001
K001 |Kit one      |P002

i need to read every record of A table and if field type is "K" then query must return its multiple product; if type is "P" then query return only one product; this is the query to accomplish

Select a.codeA AS codeExternal, 
case 
when a.type = 'K' then (select C.CODEB from C  where A.CODEA = C.CODEC)
 else a.CODEA
end as codeInternal
from 
A 
left B
ON a.CODEA = B.CODEB

but the query return only

codeExt | code Int
---------------------------
P003    | P003
K001    | P001

and not the awaited

codeExt | code Int
---------------------------
P003    | P003
K001    | P001
K001    | P002

Answer 1

try Something like this:

select A.CodeA CodeExt, B.CodeB CodeInt
from A inner join B on A.CodeA=B.CodeB and A.type='P'
union all
select A.CodeA CodeExt, C.CodeB CodeInt
from A inner join C on A.CodeA=C.CodeC and A.type='K'

if you want remove doublon, use union and not union all