We help IT Professionals succeed at work.

Complex subqueries

1,078 Views
Last Modified: 2008-02-06
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.
Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.