[RESOLVED] adding 3 select statments into one sql

[dakey]

Hi have an sql query. I have this query here:

SELECT * FROM `invoices` ORDER BY date

however i want to add to this these three queries to it, how do i do that?

SELECT SUM(sub) AS totalSub FROM lines WHERE invoice_id = invoices.id


SELECT SUM(vat) AS totalVat FROM lines WHERE invoice_id = invoices.id


SELECT SUM(total) AS totalTotal FROM lines WHERE invoice_id = invoices.id

[Krik]

You will probably want to use a LEFT JOIN to do that.

[tomhath]

Add any other columns you use from invoices in both the SELECT and GROUP BY.

SELECT invoices.id,  invoices.date,
  SUM(lines.sub) AS totalSub,
  SUM(lines.vat) AS totalVat,
  SUM(lines.total) AS totalTotal
FROM invoices
LEFT JOIN  lines  on lines.invoice_id = invoices.invoice_id
GROUP BY invoices.id,  invoices.date
ORDER BY invoices.date