• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4840
  • Last Modified:

ORACLE 9I - QUERY - CONDITIONAL JOIN

Hello
I want to be able to create a conditional join between two tables - e.g.
(bit pseudo codeish!).........

select
t1.name,
phil.test
from table1 t1
innner join  (select case when t3.category = 'RM QUOTE then t3.assigned_dept
                                                       else t4.name end as phil
                                                       from table3 t3
                                                       inner join table4 t4 on t3.name = t4.name) test
                                                       on t1.name = test.phil
I know the above does not work - any ideas how to?
Regards
Phil

 
                   
0
philsivyer
Asked:
philsivyer
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you clarify what you mean with "conditional join"?
the syntax you posted is almost correct, only one ' missing...

select
t1.name,
phil.test
from table1 t1
innner join  (select case when t3.category = 'RM QUOTE' then t3.assigned_dept
                                                       else t4.name end as phil
                                                       from table3 t3
                                                       inner join table4 t4 on t3.name = t4.name) test
                                                       on t1.name = test.phil
0
 
SJT2003ACommented:
I guess, the following is what you want.

select t1.name,test.phil
from table1 t1,(select case when t3.category = 'RM QUOTE' then t3.assigned_dept
                       else t4.name end as phil
                 from table3 t3, table4 t4
             where t3.name = t4.name) test
where t1.name = test.phil;


In Oracle, inner join is the default or simple when you just want to join two or more tables on a condition.
Here you meant conditional is that you want to display either t3.assigned_dept or t4.name conditionally, with t1.name, right?

Check it out. Good luck :)
0
 
sujith80Commented:
select t1.name, case when t3.category = 'RM QUOTE' then t3.assigned_dept else t4.name end as phil
from table3 t3 inner join table4 t4 on t3.name = t4.name
inner join table1 t1 on t1.name = case when t3.category = 'RM QUOTE then t3.assigned_dept else t4.name end
/
0
 
sujith80Commented:
select t1.name, case when t3.category = 'RM QUOTE' then t3.assigned_dept else t4.name end as phil
from table3 t3 inner join table4 t4 on t3.name = t4.name
inner join table1 t1 on t1.name = case when t3.category = 'RM QUOTE' then t3.assigned_dept else t4.name end
/
0
 
philsivyerAuthor Commented:
Hello

Thanks everyone for your contribution - I used sujith80 answer and worked a treat!
Many thanks
Phil
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now