I love this! I work with this kind of query every day.
I think It's really good, the first important thing is to reach your goal. I do the same process as you and only use inner-left joins as a standard to keep it simple (not right, not outer, etc). Then check the performance, try with thousands of records (I do this for huge reports), use "explain" and others approach to test.
I work mostly with MySQL, I can only tell you a few things: Make this query a view. In MySQL try to avoid a lot of subquerys because performance can be lost. Don't make "view of views" because the performance and maintaining this can be difficult.
I still avoid complex functions and stored procedures to keep it simple for new teammates and jr developers.
I love this! I work with this kind of query every day.
I think It's really good, the first important thing is to reach your goal. I do the same process as you and only use inner-left joins as a standard to keep it simple (not right, not outer, etc). Then check the performance, try with thousands of records (I do this for huge reports), use "explain" and others approach to test.
I work mostly with MySQL, I can only tell you a few things: Make this query a view. In MySQL try to avoid a lot of subquerys because performance can be lost. Don't make "view of views" because the performance and maintaining this can be difficult.
I still avoid complex functions and stored procedures to keep it simple for new teammates and jr developers.
Hi, Gregor.
Thanks for commenting. Yes, I'll consider using view in this case. And I do agree, that stored procedures can be tricky for new teammates.