MySQL select by subquery

Hi,

I have a query which selects an invoices oustanding balance. I am doing this using subqueries and can sort by the result 'queryBalance' but I cannot select by this 'queryBalance' in the event of wanting to return only results where the balance is 0 or > 0



 select
       Invoice.invNo,
    Customer.FirstName,
    Customer.Surname,
    Customer.Town,
    Customer.pCode,
    customer.automaticPayments,
    invoice.Gross,
    (SELECT ifnull(SUM(totPaid),0) FROM cashRcd WHERE cashRcd.invNo = invoice.invNo) AS totPaid,
    (SELECT ifNull(SUM(Gross),0) FROM creditNote WHERE creditNote.invNo = invoice.invNo) AS totCredited,
    (SELECT invoice.Gross - totCredited - totPaid ) AS queryBalance
    FROM Invoice
    INNER JOIN invoiceStatus
    ON invoice.status = invoiceStatus.statusID
    INNER JOIN Customer
    ON Invoice.CustomerId = Customer.cId
    WHERE invoice.Deleted = 0
    ORDER BY queryBalance DESC

Any ideas appreciated.

Regards
Leigh
LmillardAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
You also can use derived tables instead, so that you can perform calculation on queryBalance in the WHERE clause.
SELECT Invoice.invNo
     , Customer.FirstName
     , Customer.Surname
     , Customer.Town
     , Customer.pCode
     , Customer.automaticPayments
     , Invoice.Gross
     , IFNULL(rcpts.totPaid, 0) AS totPaid
     , IFNULL(crdts.totCredited, 0) AS totCredited
     , Invoice.Gross - IFNULL(crdts.totCredited, 0) - IFNULL(rcpts.totPaid, 0) AS queryBalance
FROM Invoice 
INNER JOIN invoiceStatus ON invoice.status = invoiceStatus.statusID
INNER JOIN Customer ON Invoice.CustomerId = Customer.cId
LEFT JOIN (
    SELECT invNo, SUM(totPaid) AS totPaid
    FROM cashRcd
    GROUP BY invNo
) rcpts ON rcpts.invNo = invoice.invNo
LEFT JOIN (
    SELECT invNo, SUM(Gross) AS totCredited
    FROM creditNote
    GROUP BY invNo
) crdts ON crdts.invNo = invoice.invNo
WHERE invoice.Deleted = 0 
AND (Invoice.Gross - IFNULL(crdts.totCredited, 0) - IFNULL(rcpts.totPaid, 0)) >= 0
ORDER BY queryBalance DESC

Open in new window

0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you can put the full query into subquery:
 select *
  from ( select
       Invoice.invNo,
    Customer.FirstName,
    Customer.Surname,
    Customer.Town,
    Customer.pCode,
    customer.automaticPayments,
    invoice.Gross,
    (SELECT ifnull(SUM(totPaid),0) FROM cashRcd WHERE cashRcd.invNo = invoice.invNo) AS totPaid,
    (SELECT ifNull(SUM(Gross),0) FROM creditNote WHERE creditNote.invNo = invoice.invNo) AS totCredited,
    (SELECT invoice.Gross - totCredited - totPaid ) AS queryBalance
    FROM Invoice
    INNER JOIN invoiceStatus
    ON invoice.status = invoiceStatus.statusID
    INNER JOIN Customer
    ON Invoice.CustomerId = Customer.cId
    WHERE invoice.Deleted = 0
 ) sq
where sq.queryBalance > 0
    ORDER BY queryBalance DESC 

Open in new window

0
 
LmillardAuthor Commented:
Both great solutions. Thanks very much - they work perfectly!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.