php - MySQL RIGHT JOIN 嵌套子查询

Question

我有以下查询，在“c”我的子查询上有右连接

$sqlbsc="SELECT customers.LastName, Sum(IFNULL(scripts.PharmAmount,0)) AS SumPharm, customers.ChemistID, customers.FullName 

   FROM 

   (SELECT scripts.WrittenOff, scripts.PharmAmount, scripts.ChemistID, scripts.ReconcilID, claims.CustomerID FROM scripts 

   INNER JOIN 

   claims ON scripts.ClaimID = claims.ClaimID 

   WHERE 

   (((scripts.WrittenOff)=False) AND ((scripts.ChemistID)='".$_SESSION['chsh']."') AND ((scripts.ReconcilID) IS NULL))) c 

   RIGHT JOIN customers ON c.CustomerID = customers.CustomerID 

   GROUP BY customers.LastName, customers.ChemistID, customers.FullName 

   HAVING ((customers.ChemistID)='".$_SESSION['chsh']."') 

   ORDER BY customers.LastName";

最终结果是

Customers.FirstName, Customers.LastName and Sum(Scripts.pharmamount) where Scripts.reconcilID IS NULL

但我希望所有客户都出现在我的客户表中，并且仅显示为 sum = $0.00

我在 Ms Access 中作为子查询工作，但上面没有给我任何结果。

我的子查询 SQL 的结果是正确的。当我使用它时，它可以工作，但我的主查询不起作用。

谁能提供一些见解吗？

Answer 1

修好了!!! 2小时..然后发布在这里，我在1分钟内修复了自己，哈哈我需要将 script.pharmamount 更改为 c.pharmamount，因为它引用的是子查询而不是实际的表!!

最终查询非常相似:

$sqlbsc="SELECT customers.LastName, Sum(IFNULL(c.PharmAmount,0)) AS SumPharm, customers.ChemistID, customers.FullName

FROM

(SELECT scripts.WrittenOff, scripts.PharmAmount, scripts.ChemistID, scripts.ReconcilID, claims.CustomerID FROM scripts

INNER JOIN

claims ON scripts.ClaimID = claims.ClaimID

WHERE

(((scripts.WrittenOff)=False) AND ((scripts.ChemistID)='".$_SESSION['chsh']."') AND ((scripts.ReconcilID) IS NULL))) c

RIGHT JOIN customers ON c.CustomerID = customers.CustomerID

GROUP BY customers.LastName, customers.ChemistID, customers.FullName

HAVING ((customers.ChemistID)='".$_SESSION['chsh']."')

ORDER BY customers.LastName";