Why Doesn't My Left Outer Join Return All Sales Representatives When I Filter by Date?

Solve the date filtering problem in SQL left outer join

In your SQL query, the left outer join did not return the expected results. Specifically, when you include a date parameter in the WHERE clause, sales representatives that do not have matching data in the right-hand table are not displayed.

To solve this problem, you need to move the date constraint of the prescriptions table into the ON condition of the join instead of keeping it in the WHERE clause:

SELECT  salesrep.salesrepid as SalesRepID,
        salesrep.fname as SalesrepFName,
        salesrep.lname as SalesRepLName,
        salesrep.fname+' '+salesrep.lname as SalesRepFullName,
        prescriber.dea_no as PDeaNo,
        prescriber.lname+', '+prescriber.fname as DocName,
        CONVERT(VARCHAR(8), prescriptions.filldate, 1) as FillDate,
        prescriptions.drugname as DrugName,
        prescriptions.daysupply as Supply,
        prescriptions.qtydisp as QtyDisp,
        prescriptions.rx_no as Refill,
        prescriptions.copay as Sample,
        ROUND(prescriptions.AgreedToPay-(prescriptions.AgreedToPay*.07),2) as AgreedToPay,
        prescriptions.carrierid as CarrierID
FROM salesrep
  LEFT OUTER JOIN prescriber on salesrep.salesrepid = prescriber.salesrepid
  LEFT OUTER JOIN prescriptions ON prescriber.dea_no = prescriptions.dea_no
                             AND prescriptions.filldate >= '09-01-12'
                             AND prescriptions.filldate <= '09-17-12'
ORDER BY prescriptions.filldate

By moving the constraint into a ON condition, you can ensure that the left outer join correctly returns results for all sales representatives, regardless of whether matching records exist in the prescriptions table.

The above is the detailed content of Why Doesn't My Left Outer Join Return All Sales Representatives When I Filter by Date?. For more information, please follow other related articles on the PHP Chinese website!