Complex subqueries

I have a table with invoice information on it and a seperate table with items on each invoice, then another table with additional data for each item on the invoice, I am getting the price of the total for all the items on the invoice by doing this:

SELECT tblinvoices.ID, sum(price*qty+(ROUND((price*qty)*taxable*(SELECT taxrate FROM tblconfig where ID=1),2))) as detailprice from tblinvoices
LEFT JOIN tblinvoicedetails ON tblinvoicedetails.invoiceid=tblinvoices.id
GROUP BY tblinvoices.ID;

Then I have to get the total price of all the extras for the items for each invoice by doing this:

SELECT tblinvoices.ID, sum(extraprice*extraqty+(ROUND((extraprice*extraqty)*taxable*(SELECT taxrate FROM tblconfig where ID=1),2))) as extraprice from tblinvoices
LEFT JOIN tblinvoicedetails ON tblinvoicedetails.invoiceid=tblinvoices.ID
LEFT JOIN tblinvoiceextras ON tblinvoicedetails.ID=tblinvoiceextras.detailid
GROUP BY tblinvoices.ID;

Then I add those two together using the invoice ID as a reference to get the total cost for the items on the invoice, this works ok for getting the total for each item and then the total for the invoice when I'm just looking up one invoice but is there a way to combine these strings into 1 string grouped by the invoice id so I can easily get a list with all the total costs for a range of invoices?

I was thinking of something like SELECT invoice.id, (query1+query2); where somehow I'm putting the invoice.id as a where clause on each query (and only returning the price).  I don't know if that's possible/the best way though.

Any advice would be appreciated.
LVL 2
Matt KendallTech / Business owner operatorAsked:
Who is Participating?
 
Angelp1ayConnect With a Mentor Commented:
You basically need the same select on each table:
****************************************

    SELECT details.ID, Sum(details.price*details.qty+(ROUND((details.price*details.qty)*details.taxable*(SELECT taxrate FROM tblconfig where ID=1),2))) as detailprice
    FROM tblinvoicedetails AS details
    GROUP BY details.ID

    SELECT extras.ID, Sum(extras.price*extras.qty+(ROUND((extras.price*extras.qty)*extras.taxable*(SELECT taxrate FROM tblconfig where ID=1),2))) as extrasprice
    FROM tblinvoiceextras AS extras
    GROUP BY extras.ID

And then join them together:
***********************

    SELECT details.ID AS detailsID, Sum(details.price*details.qty+(ROUND((details.price*details.qty)*details.taxable*(SELECT taxrate FROM tblconfig where ID=1),2))) as detailprice, joined.extrasprice
    FROM tblinvoicedetails AS details
    LEFT OUTER JOIN (
        SELECT extras.ID AS extrasID, Sum(extras.price*extras.qty+(ROUND((extras.price*extras.qty)*extras.taxable*(SELECT taxrate FROM tblconfig where ID=1),2))) as extrasprice
        FROM tblinvoiceextras AS extras
        GROUP BY extras.ID
    ) AS joined
    ON details.detailsID = joined.extrasID
    GROUP BY details.ID

...and then you just hope it works :o)
0
All Courses

From novice to tech pro — start learning today.