Left Outer Join not returning all rows

[sv89]

Hi I have the following query:

SELECT tblAppTypes.appType, tblAppTypes.appTypeID,   Count(tblOrders.OrderID) AS Orders
FROM tblAppTypes LEFT JOIN (tblApplicants LEFT JOIN tblOrders ON tblApplicants.appID = tblOrders.AppID) ON tblAppTypes.appTypeID = tblApplicants.appType
WHERE (((tblOrders.OrderID) IN (SELECT DISTINCTROW tblOrderDetails.OrderID FROM tblOrderDetails)))
GROUP BY tblAppTypes.appType, tblAppTypes.appTypeID
ORDER BY tblAppTypes.appType;

However, this does not return all rows from tblAppTypes. I think it's due to the WHERE clause. How do I fix this?

Thanks

 

[Guus2005]

The easy way: leave out the where clause.
Apparently there are orders without orderdetails.

To find them:

select * from tblorders where orderid not in (select orderidid from tblorderdetails)

HTH

 

[Banana]

Typically, when one wants a full set even though there may be other clauses that is meant to filter out another columns (e.g. we want to filter the Orders but not necessarily the AppTypes), we want to push the filter into its own subquery and left join into this.

SELECT a.appType, a.appTypeID,   Count(o.OrderID) AS Orders
FROM tblAppTypes AS a
LEFT JOIN (tblApplicants AS p 
  LEFT JOIN (
      SELECT o.AppID, o.OrderID
      FROM tblOrders AS o
      INNER JOIN tblOrderDetails AS d
        ON o.OrderID = d.OrderID
  ) AS o 
    ON p.appID = o.AppID) 
  ON a.appTypeID = p.appType
GROUP BY a.appType, a.appTypeID
ORDER BY a.appType;

Note: I converted your IN() into a join as well since there's no real need to use IN() over a join.

 

[sv89]

Thanks Banana. That worked