Problem with SQL Server Left Outer Join: Help needed

I am getting different results with LEFT outer join operator and *= operator. With *= I am getting the expected results. Can anyone look at SQL and tell what I am doing wrong?

SQL with Left Outer join operator:

select CurrentWeekFinMetrics.[Hub+],
WeeklyMetricsFormat.line#,
WeeklyMetricsFormat.MetricsType,
WeeklyMetricsFormat.Metrics,
WeeklyMetricsFormat.Measure, WeeklyMetricsFormat.jobs, case when dataformatchar is not null then
case when IsPrefix = 'Y' then dataformatchar + convert (varchar, CurrentWeekFinMetrics.displayCol )
else convert (varchar, CurrentWeekFinMetrics.displayCol ) + dataformatchar end
else
convert (varchar, CurrentWeekFinMetrics.displayCol )
end
from WeeklyMetricsFormat LEFT JOIN CurrentWeekFinMetrics on (WeeklyMetricsFormat.Line# = CurrentWeekFinMetrics.Line#)
where CurrentWeekFinMetrics.WeekEndingDate = '10/09/04' and CurrentWeekFinMetrics.[Hub+] = 'Amstelveen'
order by CurrentWeekFinMetrics.[Hub+], WeeklyMetricsFormat.Line#

SQL with *= operator
select CurrentWeekFinMetrics.[Hub+],
WeeklyMetricsFormat.line#,
WeeklyMetricsFormat.MetricsType,
WeeklyMetricsFormat.Metrics,
WeeklyMetricsFormat.Measure, WeeklyMetricsFormat.jobs, case when dataformatchar is not null then
case when IsPrefix = 'Y' then dataformatchar + convert (varchar, CurrentWeekFinMetrics.displayCol )
else convert (varchar, CurrentWeekFinMetrics.displayCol ) + dataformatchar end
else
convert (varchar, CurrentWeekFinMetrics.displayCol )
end
from WeeklyMetricsFormat , CurrentWeekFinMetrics
where CurrentWeekFinMetrics.WeekEndingDate = '10/09/04' and CurrentWeekFinMetrics.[Hub+] = 'Amstelveen'
AND (WeeklyMetricsFormat.Line# *= CurrentWeekFinMetrics.Line#)

For Left outer join operator, I am getting 54 rows, *= I am getting 69 rows.

Thanks and Regards
mbthis is really easy to explain

your LEFT JOIN query is like this -- from WeeklyMetricsFormat
LEFT
JOIN CurrentWeekFinMetrics
on WeeklyMetricsFormat.Line#
= CurrentWeekFinMetrics.Line#
where CurrentWeekFinMetrics.WeekEndingDate
= '10/09/04'
and CurrentWeekFinMetrics.[Hub+]
= 'Amstelveen'
what happens is, the optimizer first performs the left outer join according to the ON clause, then applies the WHERE conditions to the result

if there are any left rows which have no matching right rows, then all the columns from the right table will be null

therefore the WHERE conditions applied to columns of the right table effectively eliminate those unmatched rows (since nothing equals null)

what you need to do is move the conditions on the right table up into the ON clause: from WeeklyMetricsFormat
LEFT
JOIN CurrentWeekFinMetrics
on WeeklyMetricsFormat.Line#
= CurrentWeekFinMetrics.Line#
and CurrentWeekFinMetrics.WeekEndingDate
= '10/09/04'
and CurrentWeekFinMetrics.[Hub+]
= 'Amstelveen' the reason it works in the older syntax, where you have the asterisk on only one of the conditions, not all of them, is: i have no idea|||Old syntax behavior: WHERE clause is applied to the tables being joined, then performed the outer join.

_msd_

Problem with sending email

This is the first time I am setting up an operator so that I can get job
notifications to my email. When I create an operator any type in my address
and click test. It gives me the following error:
Problem occoured while attempting to resolve the address 'my email address'.
Mapi error code number -2147221233
Thanks
The first thing would be to check to ensure that SQL Mail has been setup
correctly as typically the MAPI errors occur as a result of an incorrect
configuration. The KB Article
http://support.microsoft.com/default...b;en-us;263556 describes how
to Configure SQL Mail and the KB article
http://support.microsoft.com/kb/315886/EN-US/ details Common SQL Mail
Problems.
- Peter Ward
WARDY IT Solutions
"Amit" wrote:

> This is the first time I am setting up an operator so that I can get job
> notifications to my email. When I create an operator any type in my address
> and click test. It gives me the following error:
> Problem occoured while attempting to resolve the address 'my email address'.
> Mapi error code number -2147221233
> Thanks

Problem with sending email

This is the first time I am setting up an operator so that I can get job
notifications to my email. When I create an operator any type in my address
and click test. It gives me the following error:
Problem occoured while attempting to resolve the address 'my email address'.
Mapi error code number -2147221233
ThanksThe first thing would be to check to ensure that SQL Mail has been setup
correctly as typically the MAPI errors occur as a result of an incorrect
configuration. The KB Article
http://support.microsoft.com/defaul...kb;en-us;263556 describes how
to Configure SQL Mail and the KB article
http://support.microsoft.com/kb/315886/EN-US/ details Common SQL Mail
Problems.
- Peter Ward
WARDY IT Solutions
"Amit" wrote:

> This is the first time I am setting up an operator so that I can get job
> notifications to my email. When I create an operator any type in my addres
s
> and click test. It gives me the following error:
> Problem occoured while attempting to resolve the address 'my email address
'.
> Mapi error code number -2147221233
> Thanks

Problem with sending email

This is the first time I am setting up an operator so that I can get job
notifications to my email. When I create an operator any type in my address
and click test. It gives me the following error:
Problem occoured while attempting to resolve the address 'my email address'.
Mapi error code number -2147221233
ThanksThe first thing would be to check to ensure that SQL Mail has been setup
correctly as typically the MAPI errors occur as a result of an incorrect
configuration. The KB Article
http://support.microsoft.com/default.aspx?scid=kb;en-us;263556 describes how
to Configure SQL Mail and the KB article
http://support.microsoft.com/kb/315886/EN-US/ details Common SQL Mail
Problems.
- Peter Ward
WARDY IT Solutions
"Amit" wrote:
> This is the first time I am setting up an operator so that I can get job
> notifications to my email. When I create an operator any type in my address
> and click test. It gives me the following error:
> Problem occoured while attempting to resolve the address 'my email address'.
> Mapi error code number -2147221233
> Thanks