| Use of CASE expression in Join [message #289183] |
Thu, 20 December 2007 00:33  |
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 #289193 is a reply to message #289187] |
Thu, 20 December 2007 01:10  |
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 |
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 |
 |
Michel Cadot
Messages: 68641
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
