Crystal Reports SQL Server Multiple tables and Outer Join

Question

Hi, I am trying to make a report which will show products bought by customers. I have Products, TransDetails, TransHeaders, Customers tables and i have to pick all products and for each product i have to show sales for each customer. if customer has not bought any particular product it should still be visible on report with 0 sales. user should be able to run this report for a particular customer or group of customers. The Problem i am facing right now is, if i include customers table in my sql query then it only shows products bought by customer and it doesnt show products which customer has not bought.

SELECT  Products.SalesCategory, Products.ProductCode, Products.ProductTitle, Customers.CustomerCode, Transdetails.quantity
    From    Products left outer join Transdetails on Products.ProductID= Transdetails.ProductID
            Left Outer Join Customers on Customers.CustomerId= Transdetails.CustomerID
    Where SalesCategory like 'XYZ' and Products.Status like 'A' 
    and customers.customercode like 'BEST'
    order by SalesCategory, ProductCode, ProductTitle

Answer 1

This appears to be because your Where clause includes selection on customers.customercode like 'BEST' - this essentially turns the outer join to customers into an inner join.

To select all products with SalesCategory like 'XYZ' and Status like 'A', together with any sales to customers with customercode like 'BEST', try the following:

SELECT  Products.SalesCategory, Products.ProductCode, Products.ProductTitle, Transcust.CustomerCode, Transcust.quantity
From    Products left outer join 
        (SELECT Customers.CustomerCode, Transdetails.ProductID, Transdetails.quantity
         FROM   Transdetails 
         Join   Customers on Customers.CustomerId= Transdetails.CustomerID
         WHERE  customers.customercode like 'BEST') Transcust
        on Products.ProductID= Transcust.ProductID
Where SalesCategory like 'XYZ' and Products.Status like 'A' 
order by SalesCategory, ProductCode, ProductTitle

Answer 2

by removing link between product and transaction details table i have resolved this problem so i am selecting all products and selecting all customer's transaction.