SELECT main.*, main.credits- main.debits - main.creditedamt as balance FROM ( SELECT credits = (SELECT SUM(ac.yamount) FROM account_credits ac WHERE ac.cinmates_id = @cinmates_id) , debits = (SELECT sum(ad.yamount) from account_debits ad WHERE ad.cinmates_id=@cinmates_id) , creditedamt = (SELECT sum(ad.ycreditedamt) from account_debits ad WHERE ad.cinmates_id=@cinmates_id ) ) mainI will continue to study the CROSS JOIN as I'm sure there is something in there I need to add to my arsenal as well.
>SELECT *, credits-debits-creditedamt AS balance > FROM ( > SELECT SUM(ac.yamount) AS credits > FROM account_credits ac > CROSS JOIN ( > SELECT sum(ad.yamount) AS debits, > sum(ad.ycreditedamt) AS creditedamt > FROM account_debits ad > WHERE ad.cinmates_id=@cinmates_id > ) dbt > WHERE ac.cinmates_id = @cinmates_id) > ) sm >
SELECT >> credits = (SELECT SUM(ac.yamount) >> FROM account_credits ac >> WHERE ac.cinmates_id = @cinmates_id) , >> debits = (SELECT sum(ad.yamount) >> from account_debits ad >> WHERE ad.cinmates_id=@cinmates_id) , >> creditedamt = (SELECT sum(ad.ycreditedamt) >> from account_debits ad >> WHERE ad.cinmates_id=@cinmates_id ) >>>>