"where" statement negates join

it seems when i use a udf within a where clause my left join statement is ignored. for example...

Code Snippet

select errorsandstatus.typeid, errorandstatustypes.name, errorsandstatus.value, errorsandstatus.description, count(cartonerrorandstatus.cartonentrynumber) as [count]

from errorsandstatus

join errorandstatustypes on errorsandstatus.typeid = errorandstatustypes.typeid

left outer join cartonerrorandstatus on errorsandstatus.typeid = cartonerrorandstatus.errorandstatustypeid and errorsandstatus.value=cartonerrorandstatus.errorandstatusvalue

left outer join cartonhistory on cartonerrorandstatus.cartonentrynumber = cartonhistory.entrynumber

where errorandstatustypes.availforrpt=1

group by errorsandstatus.typeid, errorandstatustypes.name, errorsandstatus.value, errorsandstatus.description

order by errorsandstatus.typeid, errorsandstatus.value

returns

id name value desc count 1 Induction 0 Inducted 140 1 Induction 1 Too Close 0 1 Induction 2 Too Small 1 1 Induction 3 Too Big 0 2 Tracking 0 Tracked 141 2 Tracking 4 Missing 0 3 Destinize 1 Valid Barcode 3 3 Destinize 2 InValid Barcode 0

now when i use the exact same query only this time include 3 udf's within the where clause the left join seems to be ignored

Code Snippet

select errorsandstatus.typeid, errorandstatustypes.name, errorsandstatus.value, errorsandstatus.description, count(cartonerrorandstatus.cartonentrynumber) as [count]

from errorsandstatus

join errorandstatustypes on errorsandstatus.typeid = errorandstatustypes.typeid

left outer join cartonerrorandstatus on errorsandstatus.typeid=cartonerrorandstatus.errorandstatustypeid and
errorsandstatus.value=cartonerrorandstatus.errorandstatusvalue

left outer join cartonhistory on cartonerrorandstatus.cartonentrynumber = cartonhistory.entrynumber

where errorandstatustypes.availforrpt=1 and dbo.udf_IsDateInRange(CartonHistory.Completed, @.DateRangeStart, @.DateRangeEnd) = 1 AND dbo.udf_IsDayInShift(CartonHistory.Completed, @.ShiftDaysOfWeek) = 1 AND dbo.udf_IsTimeInShift(CartonHistory.Completed, @.ShiftTimeStart, @.ShiftTimeEnd) = 1

group by errorsandstatus.typeid, errorandstatustypes.name, errorsandstatus.value, errorsandstatus.description

order by errorsandstatus.typeid, errorsandstatus.value

returns

id name value desc count 1 Induction 0 Inducted 26 2 Tracking 0 Tracked 26 3 Destinize 1 Valid Barcode 3 3 Destinize 4 No Read 4 4 Divert 1 Validated 6 4 Divert 3 Lane Full 1 5 WtStatus 1 Valid Weight 2 7 Barcode 1 Valid Read 3 7 Barcode 2 No Read 4

why does it ignore the left join when i evaluate the udf's in the where clause?

thanks!

Try to move parts of your WHERE statement into ON clause of desired left join|||

amazing...moved it into the second join and presto! thank you!

select errorsandstatus.typeid, errorandstatustypes.[name], errorsandstatus.value, errorsandstatus.[description],
count(cartonerrorandstatus.cartonentrynumber) as [count]
from errorsandstatus
join errorandstatustypes on errorsandstatus.typeid = errorandstatustypes.typeid
left outer join cartonerrorandstatus on errorsandstatus.typeid=cartonerrorandstatus.errorandstatustypeid and
errorsandstatus.value=cartonerrorandstatus.errorandstatusvalue
left outer join cartonhistory on cartonerrorandstatus.cartonentrynumber = cartonhistory.entrynumber
and dbo.udf_IsDateInRange(CartonHistory.Completed, @.DateRangeStart, @.DateRangeEnd) = 1
AND dbo.udf_IsDayInShift(CartonHistory.Completed, @.ShiftDaysOfWeek) = 1
AND dbo.udf_IsTimeInShift(CartonHistory.Completed, @.ShiftTimeStart, @.ShiftTimeEnd) = 1
where errorandstatustypes.availforrpt=1
group by errorsandstatus.typeid, errorandstatustypes.[name], errorsandstatus.value, errorsandstatus.[description]
order by errorsandstatus.typeid, errorsandstatus.value

|||The key to remember is that anytime you have a field from the right table in the WHERE clause, you effectively convert into an 'equi-JOIN'. But if what you are really attempting to do is filter the right table, do so in the JOIN conditions.|||thanks for the clarification arnie!sql