[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ORACLE 9I - QUERY - CONDITIONAL JOIN

Posted on 2007-11-21
5
Medium Priority
?
4,728 Views
Last Modified: 2013-12-19
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
Comment
Question by:philsivyer
5 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 20328813
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
 
LVL 6

Assisted Solution

by:SJT2003A
SJT2003A earned 200 total points
ID: 20330438
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
 
LVL 27

Expert Comment

by:sujith80
ID: 20333406
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
 
LVL 27

Accepted Solution

by:
sujith80 earned 1600 total points
ID: 20333408
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
 

Author Comment

by:philsivyer
ID: 20333563
Hello

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses
Course of the Month19 days, 19 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question