| Re: Outer joins [message #373729] |
Thu, 03 May 2001 12:09  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
to do the type of joins you are after t1->t2->t3, you can have two outer joins, but you must first resolve the one and then the other. The form is something like:
select t1.id , x.date , x.value
from tb_ids t1, (select t3.id, t2.date , t3.value from tb_dates t2, tb_values t3
where t2.date = t3.date (+)) x
where t1.id = x.id (+);
|
|
|
|
| Re: Outer joins [message #373730 is a reply to message #373729] |
Thu, 03 May 2001 12:29  |
nomit
Messages: 1
Registered: May 2001
|
Junior Member |
|
|
sorry that didn't work
I'll try using an example
I would like a query to return:
Id Date Value
ANT 31/01/00 100
ANT 28/02/01 50
ANT 31/03/01 0
when tb_value contains (as well as other ids )
Id Date Value
ANT 31/01/00 100
ANT 28/02/01 50
,tb_ids contains (as wel as other ids)
id
ANT
and Td_dates contains only
Date
31/01/00
28/02/01
31/03/01
so that the constraints on tb_value to the other 2 tables bring back a zero value for that id at that date
should the above query do that?
cheers for allyour help
|
|
|
|
| Re: Outer joins [message #373732 is a reply to message #373729] |
Thu, 03 May 2001 13:46 |
Sundar Venkatasubramaniam
Messages: 26
Registered: May 2001
|
Junior Member |
|
|
You want something like this
select t3.id,t3.date,t3.value from
tb_value t3 union all
select t1.id,t2.date ,to_number(null) from tb_ids t1,tb_dates t2
where not exists(select 1 from tb_value where
id = t1.id and date = t2.date)
I enclosed null wih to_number by asuming t3.value is number filed. If it char field no need of this enclosure.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
