Solved

ORACLE 9I - QUERY - CONDITIONAL JOIN

Posted on 2007-11-21
5
4,530 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 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 50 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 400 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

Independent Software Vendors: 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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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…
This video shows how to recover a database from a user managed backup

730 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