?
Solved

ORACLE 9I - QUERY - CONDITIONAL JOIN

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

764 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