mercredi 14 janvier 2015

Translating subquery to left join in sqlite

I have a query that is running against a SQLite database that uses a couple of subqueries. In order to accommodate some new requirements, I need to translate it to use joins instead. Below is the structure version of the original query:



SELECT c.id AS category_id, b.budget_year,
(
SELECT sum(actual)
FROM lines l1
WHERE status = 'complete'
AND category_id = c.id
AND billing_year = b.budget_year
) AS actual
(
SELECT sum(planned)
FROM lines l2
WHERE status IN ('forecasted', 'in-progress')
AND category_id = c.id
AND billing_year = b.budget_year
) AS rough_proposed
FROM categories AS c
LEFT OUTER JOIN budgets AS b ON (c.id = b.category_id)
GROUP BY c.id, b.budget_year;


The next query is my first attempt to convert it to use LEFT OUTER JOINs:



SELECT c.id AS category_id, b.budget_year, sum(l1.actual) AS actual, sum(l2.planned) AS planned
FROM categories AS c
LEFT OUTER JOIN budgets AS b ON (c.id = b.category_id)
LEFT OUTER JOIN lines AS l1 ON (l1.category_id = c.id
AND l1.billing_year = b.budget_year
AND l1.status = 'complete')
LEFT OUTER JOIN lines AS l2 ON (l2.category_id = c.id
AND l2.billing_year = b.budget_year
AND l2.status IN ('forecasted', 'in-progress'))
GROUP BY c.id, b.budget_year;


However, the actual and rough_proposed columns are much larger than expected. I am no SQL expert, and I am having a hard time understanding what is going on here. Is there a straightforward way to convert the subqueries to joins?


Aucun commentaire:

Enregistrer un commentaire