Problem with left join

Hi,
I have a query that is supposed to return records and make a left join where
one field is not null, but for some reason is not working properly and
returns the records even though they are null.
SELECT *
FROM cases a
left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
MAX(y.Dateinitiated)
From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
Any help is greately appreciated.
AleksBetter if you post some ddl, sample data and expected result.
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Aleks" wrote:

> Hi,
> I have a query that is supposed to return records and make a left join whe
re
> one field is not null, but for some reason is not working properly and
> returns the records even though they are null.
> --
> SELECT *
> FROM cases a
> left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
> MAX(y.Dateinitiated)
> From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
> y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
> --
> Any help is greately appreciated.
> Aleks
>
>|||Aleks,
The reason the query seems to be returnning records from Activities,
where the Dateinitiated column is null, is because you have specified an
Outer Join.
When you specify An Outer Join, Al records from the Outer table are
returned, even when there is no match on the other side. You actually are
NOT returning any data from Activities Table where Dateinitiated column is
null. If you look at those rows, you'll probably notice that all the field
s
from Activities table are null there...
"Aleks" wrote:

> Hi,
> I have a query that is supposed to return records and make a left join whe
re
> one field is not null, but for some reason is not working properly and
> returns the records even though they are null.
> --
> SELECT *
> FROM cases a
> left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
> MAX(y.Dateinitiated)
> From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
> y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
> --
> Any help is greately appreciated.
> Aleks
>
>