Crystal Reports: SQL Left Join

• Crystal Reports: SQL introduction

• Crystal Reports: Generate SQL to Change Selected Records in Crystal Reports

• Crystal Reports: SQL Commands in Crystal Reports

• Crystal Reports: Useful SQL Utility: Crystal SQL Designer

• Crystal Reports: SQL Commands for Creating Audit Records in Crystal Reports

• Crystal Reports: SQL Expressions and Sub Selects

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.