Crystal Reports: SQL Left Join

There are many ways to do a left join to a table with record selection. Luqman at Pearl Soft in Pakistan provided this SQL-based method of doing so. Use a SQL select statement as follows:

SELECT A.CUSTOMERID, SUM(C.QUANTITY) FROM CUSTOMERS A
LEFT JOIN ORDERS B ON A.CUSTOMERID=B.CUSTOMERID
LEFT JOIN [ORDER DETAILS] C ON B.ORDERID=C.ORDERID
ND B.ORDERDATE BETWEEN '19960704' AND '19960730'
GROUP BY A.CUSTOMERID

The secret is to set your filter in the JOIN clause, rather than the WHERE clause.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.