is this nested select possible?

[ingernet]

So I have two tables: CASES and REACTIONS.

in CASES, I have CASE_ID, REACTION_ID_EYES and REACTION_ID_SYSTEM.

in REACTIONS, I have REACTION_ID, REACTION_TYPE and REACTION_NAME.

so it looks like this:

[tt]
CASES
Case_ID Reaction_ID_Eyes Reaction_ID_System
42 13 16
43 14 15
44 13 <NULL>
45 <NULL> 16

REACTIONS
Reaction_ID Reaction_Name Reaction_Type
13 pink eye eyes
14 dry eye eyes
15 cooties system
16 funk system


Ultimately, I'd like to be able to retrieve data that looks like this:

CASE_ID REACTION_NAME_EYES REACTION_NAME_SYSTEM
42 pink eye funk
43 dry eye cooties
44 pink eye (none)
45 (none) funk

...but because one of the reactions for a particular case might be blank, i'm having trouble with the SELECT statement.

anyone have any ideas?

Thanks,
inger

 

[sunila7]

hi,

try this query

SELECT C.Case_ID, R1.Reaction_name,R2.Reaction_Name FROM
[Case] C
Left Outer Join Reactions R1 ON C.Reaction_ID_Eyes = R.Reaction_ID
Left Outer Join Reactions R2 ON C.Reaction_ID_System = R2.Reaction_ID

Sunil

 

[EBrownSQL]

Your select statement should look like this:


Select c.case_id as 'CASE_ID',

case when c.reaction_id_eyes is null then
'(none)'
else
(select reactions.reaction_name_eyes from reactions
where reactions.reaction_id = c.reaction_id_eyes)
end as 'REACTION_NAME_EYES',

case when c.reaction_id_system is null then
'(none)'
else
(select reactions.reaction_name_system from reactions
where reactions.reaction_id = c.reaction_id_system)
end as 'REACTION_NAME_SYSTEM'

From cases

Hope this works for you.

 

[ingernet]

Thanks for following up so quickly! I ended up getting it working with outer joins. Phew!

Inger