I have the following tables:
- agencies: | code |
- commissions: | registered_at | action | origin | destination | commission |
I need to SUM(commissions.commission) for the following conditions
- agencies.code = commissions.origin HAVING commissions.action = "collection"
- agencies.code = commissions.destination HAVING commissions.action = "delivery"
WHERE Date(commissions.registered_at) between '2023-01-01 00:00:00' and '2023-01-31 23:59:59' Group By agencies.code
As you can see, these 2 tables have no PK / FK relations.
They are related by the destination / origin and the action being queried.
I tried to make a double Left Join on commissions, but that seems to call the second join for the number of records from the first join. I also don't see how I can make it work with IF conditions inside SELECT, as the conditions to join the tables are different.
Any help is appreciated.