join child table for 12 rows on date

Question

I need to write a query on a parent table that has child records based on year and month and I want the values from the previous 12 months as a single row. The query below works and speed isn't terrible, but I would like to learn if there is a faster way and how to handle the period column.

The period column has the value (year * 100 + month), e.g. for 2022-07-01, period = 202207 (2022 * 100 + 7). When I call the query I know date of the bnsm12 join. bnsm11 is known date minus 1 month, bnsm10 is known date minus 2 months, etc.

What is a better way to do this?

SELECT
bn.id, bn.accountno,
bnsm01.period, bnsm01.balance,
bnsm02.period, bnsm02.balance,
bnsm03.period, bnsm03.balance,
bnsm04.period, bnsm04.balance,
bnsm05.period, bnsm05.balance,
bnsm06.period, bnsm06.balance,
bnsm07.period, bnsm07.balance,
bnsm08.period, bnsm08.balance,
bnsm09.period, bnsm09.balance,
bnsm10.period, bnsm10.balance,
bnsm11.period, bnsm11.balance,
bnsm12.period, bnsm12.balance
FROM parents AS bn
LEFT JOIN parentmonthly AS bnsm01 ON bnsm01.parentId = bn.id AND bnsm01.period = (2021 * 100) + 05
LEFT JOIN parentmonthly AS bnsm02 ON bnsm02.parentId = bn.id AND bnsm02.period = (2021 * 100) + 06
LEFT JOIN parentmonthly AS bnsm03 ON bnsm03.parentId = bn.id AND bnsm03.period = (2021 * 100) + 07
LEFT JOIN parentmonthly AS bnsm04 ON bnsm04.parentId = bn.id AND bnsm04.period = (2021 * 100) + 08
LEFT JOIN parentmonthly AS bnsm05 ON bnsm05.parentId = bn.id AND bnsm05.period = (2021 * 100) + 09
LEFT JOIN parentmonthly AS bnsm06 ON bnsm06.parentId = bn.id AND bnsm06.period = (2021 * 100) + 10
LEFT JOIN parentmonthly AS bnsm07 ON bnsm07.parentId = bn.id AND bnsm07.period = (2021 * 100) + 11
LEFT JOIN parentmonthly AS bnsm08 ON bnsm08.parentId = bn.id AND bnsm08.period = (2021 * 100) + 12
LEFT JOIN parentmonthly AS bnsm09 ON bnsm09.parentId = bn.id AND bnsm09.period = (2022 * 100) + 01
LEFT JOIN parentmonthly AS bnsm10 ON bnsm10.parentId = bn.id AND bnsm10.period = (2022 * 100) + 02
LEFT JOIN parentmonthly AS bnsm11 ON bnsm11.parentId = bn.id AND bnsm11.period = (2022 * 100) + 03
LEFT JOIN parentmonthly AS bnsm12 ON bnsm12.parentId = bn.id AND bnsm12.period = (2022 * 100) + 04
WHERE bn.id BETWEEN 16620 AND 16650;

when you are joining to the same table this many times in the query, it certainly would get slower as the size of the table increases - do you have index on that parent table on id and period columns ? why not `GROUP By id, period` ? it will get you as rows, and then you could use PIVOT [in mssql, not sure what is equivalent in your db] — Anand Sowmithiran, Jul 19 '22 at 03:31
As PIVOT is not there in mysql, using CASE , aggregate function and GROUP By , equivalent result can be obtained. see this SO [answer](https://stackoverflow.com/a/12005676/14973743) — Anand Sowmithiran, Jul 19 '22 at 03:36
Thanks for the responses. I have continued with how I was doing this, but appreciate the info. — user3720435, Jul 26 '22 at 22:35

join child table for 12 rows on date

0 Answers0