3

I have the following tables:

  1. agencies: | code |
  2. commissions: | registered_at | action | origin | destination | commission |

I need to SUM(commissions.commission) for the following conditions

  1. agencies.code = commissions.origin HAVING commissions.action = "collection"
  2. 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.

2
  • 2
  • I don't have time to create a full answer, just making a comment instead. If you don't need any data from the agencies table you might not need to use that table at all, you might be able to do everything from the commissions table.
    – Polygorial
    Jan 31 at 22:14

3 Answers 3

2

If registered_at is indexed (with registered_at as the first column ot only column) in the commissions table, then there is a better way to expression the WHERE clauses on registered_at.

This WHERE clause

WHERE DATE(registered_at) BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59'

will trigger a table scan for sure because of the DATE() function on the left side.

Please change

WHERE DATE(registered_at) BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59'

to

  WHERE registered_at >= '2023-01-01 00:00:00' AND registered_at <= '2023-01-31 23:59:59'

This will definitely change the EXPLAIN plan to hopefully use the index.

So the query should look like this

SELECT agencies.code, 
       COALESCE(collections.collections, 0) as collections,
       COALESCE(deliveries.deliveries, 0) as deliveries
FROM agencies
LEFT JOIN (
  SELECT origin as code, 
         SUM(IF(action = 'collection', commission, 0)) as collections,
         SUM(IF(action = 'delivery', commission, 0)) as deliveries
  FROM commissions
  WHERE registered_at >= '2023-01-01 00:00:00' AND registered_at <= '2023-01-31 23:59:59'
  GROUP BY origin
) collections ON agencies.code = collections.code
LEFT JOIN (
  SELECT destination as code, 
         SUM(IF(action = 'collection', commission, 0)) as collections,
         SUM(IF(action = 'delivery', commission, 0)) as deliveries
  FROM commissions
  WHERE registered_at >= '2023-01-01 00:00:00' AND registered_at <= '2023-01-31 23:59:59'
  GROUP BY destination
) deliveries ON agencies.code = deliveries.code;

If there is no registered_at index in the commissions table, then create one

ALTER TABLE commissions
    ADD INDEX covering_index (registered_at,action,commission)
   ,ALGORITHM=INPLACE
   ,LOCK=NONE
;

This will create short index scans and not touch the table.

1
  • This seems a more complete answer than my own. Also thank you for alternative suggestion instead of BETWEEN. Did not know.
    – user268130
    Feb 1 at 14:00
1

EDIT

Correct answer is:

SELECT agencies.code, 
       COALESCE(collections.collections, 0) as collections,
       COALESCE(deliveries.deliveries, 0) as deliveries
FROM agencies
LEFT JOIN (
  SELECT origin as code, 
         SUM(IF(action = 'collection', commission, 0)) as collections,
         SUM(IF(action = 'delivery', commission, 0)) as deliveries
  FROM commissions
  WHERE DATE(registered_at) BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59'
  GROUP BY origin
) collections ON agencies.code = collections.code
LEFT JOIN (
  SELECT destination as code, 
         SUM(IF(action = 'collection', commission, 0)) as collections,
         SUM(IF(action = 'delivery', commission, 0)) as deliveries
  FROM commissions
  WHERE DATE(registered_at) BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59'
  GROUP BY destination
) deliveries ON agencies.code = deliveries.code;

This seems to be the correct solution.

Previous answer - Incorrect:

SELECT agencies.code, 
       SUM(IF(commissions.action = 'collection', commissions.commission, 0)) as collections,
       SUM(IF(commissions.action = 'delivery', commissions.commission, 0)) as deliveries
FROM agencies
LEFT JOIN commissions ON agencies.code = commissions.origin OR agencies.code = commissions.destination
WHERE DATE(commissions.registered_at) BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59'
GROUP BY agencies.code;

This seemed to be working alright at first, but all results had some added value that I did not manage to find out the cause of.

3
0

(Not an "Answer", but some comments that may help clarify things:)

  • You can JOIN tables without declaring a FOREIGN KEY.

  • You can JOIN tables without declaring an INDEX. (But it is likely to be slower.)

  • A FOREIGN KEY provides (1) an INDEX and (2) a constraint that is checked -- to see if, during an INSERT, there is a corresponding row in the other table (3) a way to CASCADE certain things. I repeat, it is not a prerequisite for JOINing.

  • A "self join" is when you need the same table twice, for eaxmple

      FROM tbl AS t1
      JOIN tbl AS t2 ON t1.a = t2.b+1
    
  • Similarly you can JOIN to the same table twice:

      FROM tbl
      JOIN other_table AS t2 ON tbl.a = t2.b
      JOIN other_table AS t3 ON tbl.x = t3.y
    
  • When practical, use WHERE instead of HAVING. Aggregates must be in HAVING, not WHERE. If you tried to put such in WHERE, MySQL will spit at you.

  • (minor note) I recommend instead of

      x between '2023-01-01 00:00:00' and '2023-01-31 23:59:59'
    

doing

         x >= '2023-01-01'
     AND x  < '2023-01-01' + INTERVAL 1 MONTH
  • More importantly, Date(commissions.registered_at) is not sargable ; all you need is just commissions.registered_at

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.