Task 11:

Display only customers that have orders or payments

The database contains customers that do not have orders or payments. The query for the customers report returns all customers. When you run the report, there are customer rows that show only the column headings for the Orders and Payments tables, as shown in Figure 13‑25.

Figure 13‑25

Report shows no order or payment data for one customer

You can exclude customers that do not have orders or payments by changing the query for the customers report.

1	Choose Layout to return to the layout editor.

2	In Data Explorer, expand Data Sets, right-click Customers, then choose Edit.

3	Add the following SQL lines to the end of the existing query:

WHERE

EXISTS

(SELECT Orders.customerNumber

FROM Orders

WHERE Customers.customerNumber =

Orders.customerNumber)

OR

EXISTS

(SELECT Payments.customerNumber

FROM Payments

WHERE Customers.customerNumber =

Payments.customerNumber)

The WHERE EXISTS clause checks the Orders and Payments tables for customerNumber values that match the customerNumber values in the Customers table. Only rows that have matching customerNumber values are selected. The complete query should look like the one shown in Figure 13‑26.

Figure 13‑26

Updated SELECT query in Edit Data Set

4	Choose Preview Results to verify that the query returns rows, then choose OK to save the change to the data set.

5	Preview the report. Scroll down the report to check the output. The report no longer displays customers that do not have orders or payments.