lundi 12 octobre 2015

Sqlite 4 table join displaying all records

I have 4 tables, project, project_status, invoice and purchase.

I have the following query I have created, jut can't get my head around the type of joins with more than 2 tables. The query I have created seems to only be finding one record, but what I would really like is display all the records from the project table between project_status 1 - 4, regardless whether the project has many related invoice/purchase records. The related invoice/purchase data should be summed

Thanks

SELECT  project.projectID, project.project_title, 
  sum(invoice.invoice_net), sum(purchase.total_order)
  FROM project
  left JOIN invoice
  ON project.projectID=invoice.projectID
  left JOIN purchase
  ON project.projectID=purchase.projectID
  LEFT JOIN project_status
  ON project.project_statusID=project_status.project_statusID
  WHERE project.project_statusID BETWEEN 1 AND 5

Aucun commentaire:

Enregistrer un commentaire