morechocolate
Technical User
I wrote an outer join query using the more current syntax, but I am not getting the desired results. However, when I use the old syntax for outer joins, *=, I get the desired results.
Table 1
number company subco date balance
1 jazzco contemp 8/25/2003 100
2 jazzco classic 8/25/2003 200
1 gosco singco 8/25/2003 200
Table 2
number company subco date balance name bal2
1 jazzco contemp 8/25/2003 100 dave 25
2 jazzco classic 8/25/2003 200 mithc 10
Desired result
number company subco date balance name bal2
1 jazzco contemp 8/25/2003 100 dave 25
2 jazzco classic 8/25/2003 200
1 gosco singco 8/25/2003 200
Query using newest syntax does get above
select t1.number,t1.company,t1.subco,t1.date,t1.balance, t2.name,t2.bal2
from table t1
left outer join table 2 on
t1.number = t2.number and
t1.company = t2.company and
t1.subco = t2.subco and
t1.date = t2.date
where t2.name = 'dave' and
t2.date = '2003-08-25'
I get
number company subco date balance name bal2
1 jazzco contemp 8/25/2003 100 dave 25
However using the old *= syntax I get the desired results.
Why the difference?
BTW, I can get the desired results using syntax one, if I use a subquery. But why should I, if it works the old way?
Thanks
Table 1
number company subco date balance
1 jazzco contemp 8/25/2003 100
2 jazzco classic 8/25/2003 200
1 gosco singco 8/25/2003 200
Table 2
number company subco date balance name bal2
1 jazzco contemp 8/25/2003 100 dave 25
2 jazzco classic 8/25/2003 200 mithc 10
Desired result
number company subco date balance name bal2
1 jazzco contemp 8/25/2003 100 dave 25
2 jazzco classic 8/25/2003 200
1 gosco singco 8/25/2003 200
Query using newest syntax does get above
select t1.number,t1.company,t1.subco,t1.date,t1.balance, t2.name,t2.bal2
from table t1
left outer join table 2 on
t1.number = t2.number and
t1.company = t2.company and
t1.subco = t2.subco and
t1.date = t2.date
where t2.name = 'dave' and
t2.date = '2003-08-25'
I get
number company subco date balance name bal2
1 jazzco contemp 8/25/2003 100 dave 25
However using the old *= syntax I get the desired results.
Why the difference?
BTW, I can get the desired results using syntax one, if I use a subquery. But why should I, if it works the old way?
Thanks