Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ORACLE 9I - QUERY - CONDITIONAL JOIN

Posted on 2007-11-21
5
Medium Priority
?
4,696 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 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

618 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