Glenn Angel
Glenn Angel

Reputation: 479

MySQL RIGHT JOIN nested subquery

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

Answers (1)

Glenn Angel
Glenn Angel

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

Related Questions