左外连接不起作用? [英] Left Outer Join Not Working?

问题描述

我有一个查询，使用两个连接的LEFT OUTER JOIN从三个表中提取数据.我需要查询以返回最左边的(Salesrep表)信息，即使在两个右边的表(分别是处方药和处方)中没有对应的数据也是如此.当我在WHERE子句中没有日期参数的情况下运行此查询时，我得到了预期的回报，但是当我包含日期参数时，在没有与salesrep相匹配的数据的情况下，我一无所获.我至少需要查看查询中请求的salesrep表列.

I have a query pulling data from three tables using LEFT OUTER JOIN for both joins. I need the query to return the left most (Salesrep table) info even if the there is no corresponding data in the two right tables (prescriber and prescriptions, respectively). When I run this query without the date parameters in the WHERE clause, I get the expected return, but as soon as I include the date parameters I get nothing returned where there is no matching data for a salesrep. I need to at least see the salesrep table columns requested in the query.

这是查询...非常感谢您的帮助.

Here is the query... any help is VERY much appreciated.

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
  WHERE salesrep.salesrepid = 143 AND
        prescriptions.filldate >= '09-01-12' AND
        prescriptions.filldate <= '09-17-12'
ORDER BY prescriptions.filldate

推荐答案

您应该将prescriptions.filldate上的约束移到联接的ON条件中，并将其从where子句中删除:

You should move the constraints on prescriptions.filldate into the ON condition of the join, and remove it from the where clause:

LEFT OUTER JOIN prescriptions ON prescriber.dea_no = prescriptions.dea_no
                             AND prescriptions.filldate >= '09-01-12'
                             AND prescriptions.filldate <= '09-17-12'

否则，没有prescriptions的条目以prescriptions.filldate中的null结尾，并且WHERE子句将其丢弃.

Otherwise, entries for which there are no prescriptions end up with nulls in prescriptions.filldate, and the WHERE clause throws them away.

这篇关于左外连接不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！