What does this work for the old syntax, but not the new

[morechocolate]

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

 

[morechocolate]

The first query also works if I do

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 and
t2.name = 'dave'
where t2.date = '2003-08-25'

Something seems wrong about that and another tool I am using will not allow that. I automatically buts the t2.name = 'dave' in the where clause.

 

[MonsterBuck]

Think of the syntax this way for outer joins...
whatever you put in the on clause will join the two tables.

Then you have your where clause. That will filter the results of the joined tables.

So on a left outer join
where t2.name = 'dave' is in the where clause

the left outer join gives this:
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

and now the where clause of t2.name = 'dave' kicks in. And you only get this for results
number company subco date balance name bal2
1 jazzco contemp 8/25/2003 100 dave 25


If you put the where t2.name = 'dave' in the on clause, you join the table and those results are:
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

and there isn't anything in the where clause to filter the results down further.

Hopefully that will help explain it more.

 

[JamesLean]

The query that works (with the filter in the JOIN predicate rather than the WHERE clause) is the best way to filter on an outer join-ed table without losing rows you don't want to. However, if you need to put it in the WHERE clause then you can do:

...
WHERE (t2.name = 'dave' OR t2.name IS NULL)
  AND t2.date = '2003-08-25'

--James

 

[morechocolate]

James,

I had tried the second method you mentioned above and it did not yeild the desired results either.

Thanks for your help though.