>Not being aware of the issue I'm about to talk about, someone might possibly write the following query, using an OUTER JOIN between the Vendor table and the PO Header table to get all 104 vendors.
>
>

>  select Vend.Name, SUM(TotalDue) as VendorTotal
>      from  Purchasing.Vendor   as Vend
>         left outer  join  Purchasing.PurchaseOrderHeader AS POH  
>               on Vend.BusinessEntityID = POH.VendorID
>                    where OrderDate BETWEEN '1-1-2008' AND '12-31-2008'     -- want just order dollars for 2008
>         group by Vend.Name   
>         order by VendorTotal desc
>

>The results only show 86 vendors (just the vendors who had orders in 2008). The numbers are correct for those 86 vendors, but we don't see the 18 vendors who didn't have orders.

>But why??? We did a LEFT OUTER JOIN between the Vendor table and the PO Header table, so that should have given us all 104 vendors.
>
>Here's the reason - the WHERE clause on the child table (POHeader) to filter the orders for 2008 is essentially negating the effect of the OUTER JOIN - giving us just the vendors who had orders in 2008.

>So what can we do? Well, there are several answers. One might be to check the Order Date column being null, like so...

>

>...where OrderDate BETWEEN '1-1-2008' AND '12-31-2008'  OR OrderDate IS NULL   
>

>
>That will certainly work - however if there's an index on OrderDate, SQL Server won't be able to do an index seek - it will do an index scan, far less optimal.

>An arguably better approach is to use some kind of subquery or common table expression to retrieve the orders for 2008 - and then do an OUTER JOIN from the Vendor Master to the results of the subquery/CTE...
>
>

>   ;WITH POTemp as
>      ( SELECT VendorID, SUM(TotalDue) as VendorTotal 
>                FROM Purchasing.PurchaseOrderHeader
>                WHERE	 where OrderDate BETWEEN '1-1-2008' AND '12-31-2008'  
>                  GROUP BY VendorID) 
>   
>  select Vend.Name,  VendorTotal
>    from  Purchasing.Vendor   as Vend
>      left outer   join POTemp
>               on Vend.BusinessEntityID = POTemp.VendorID
>      order by VendorTotal desc
>

There is no need for a CTE. IMO, the single best approach is to include the where clause into the join itself

  select Vend.Name, SUM(TotalDue) as VendorTotal
      from  Purchasing.Vendor   as Vend
         left outer  join  Purchasing.PurchaseOrderHeader AS POH  
               on Vend.BusinessEntityID = POH.VendorID AND POH.OrderDate BETWEEN '1-1-2008' AND '12-31-2008'   
         group by Vend.Name   
         order by VendorTotal desc

BTW as a good practise, in SQL you format your dates in ANSI format ('yyyymmdd') as the american format scares the hell out of non-us programmers.

Through i recognise the place of CTE's , I really hate to use them, and would avoid them whenever possible. One of the problems is that it is not an ANSI standard and often there is a better (readable and maintainable) alternative.



Walter,