Reputation: 479
I have the below query with a right join on "c" my subequery
$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";
This is to end up with
Customers.FirstName, Customers.LastName and Sum(Scripts.pharmamount) where Scripts.reconcilID IS NULL
But i want ALL my customers to appear from my customers table and just appear as sum = $0.00
I have ti working in Ms Access as a subquery, but above gives me nothing.
My results are correct for my subquery SQL. When i use this it works, but my main query is not working.
Can anyone give some insight?
Upvotes: 0
Views: 161
Reputation: 479
fixed it!!! 2 hours.. then posted on here and i fix myself in 1 min lol I needed to change scripts.pharmamount to c.pharmamount as it refers to the subquery not the actual table!!
Final Query was VERY similar:
$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";
Upvotes: 1