Home » Other » Training & Certification » Use of CASE expression in Join
Use of CASE expression in Join [message #289183] Thu, 20 December 2007 00:33 Go to next message
krist01
Messages: 2
Registered: December 2007
Location: HYD
Junior Member
Hi All ,
I have a problem . I have 2 queries QA and QB

I have 3 tables A,B,C,
1. Join B.id1 with A.id
2.Join C.id1with A.id

I want to Join C.id1 with B.id only if first join condition returns some value. So I want to use case expr. Please clarify
Re: Use of CASE expression in Join [message #289187 is a reply to message #289183] Thu, 20 December 2007 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you use the 2 joins at the same time then the returned rows only contain those who satisfy both joins and so first one.

Regards
Michel
Re: Use of CASE expression in Join [message #289193 is a reply to message #289187] Thu, 20 December 2007 01:10 Go to previous messageGo to next message
krist01
Messages: 2
Registered: December 2007
Location: HYD
Junior Member
Thanx michel. But I want to join C.id1 with B.id based on above join condition . As of now I have done in this pattern below

join B on B.id = A.id
join C ON C.id = (CASE WHEN B.id is null then A.id else B.id)

but I am unable to fetch rows of join C with A .
I want to join C with both A and B based on above join condition.

[Updated on: Thu, 20 December 2007 01:16]

Report message to a moderator

Re: Use of CASE expression in Join [message #289243 is a reply to message #289183] Thu, 20 December 2007 07:22 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
You are a bit vague, but something like this might be a starting point. If b.id is null the 1st query is empty.
select a.id from
a,b where
a.id = b.id 
UNION ALL
select c.id from a,b,c
where c.id = nvl(b.id,a.id)
Re: Use of CASE expression in Join [message #289259 is a reply to message #289193] Thu, 20 December 2007 08:01 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Converted what you said in SQL it is:
select ...
from a, b, c
where b.id (+) = a.id
  and c.id = nvl(b.id,a.id)
/

But logically it is always: c.id = a.id
Just a thought, if first join works then b.id = a.id and searching for b.id is the same as searching for a.id. Now if it does work then b.id is null and you search for a.id.
In both cases, you search for a.id.

Regards
Michel
Previous Topic: Does oracle certification expires?
Next Topic: help with new member function
Goto Forum:
  


Current Time: Thu Dec 08 12:43:06 CST 2016

Total time taken to generate the page: 0.06639 seconds