I came across this SQL at work, This was written by my colleague. Although there are better solutions, I’m just curious,and now I have simplified it as follows:
-- Calculate the total of the 'APPROVING' salary and the 'AGENT' salary already actual paid
SELECT ifnull(sum(l.salary),0) +
(SELECT ifnull(sum(l1.salary),0)
FROM salary_header h1 JOIN salary_lines l1
ON h1.salary_id = l1.salary_id
WHERE h1.status='APPROVING' AND l1.project_id = l.project_id)
FROM salary_pay_headers h JOIN salary_pay_lines l
ON h.salary_pay_id = l.salary_pay_id
WHERE h.pay_type='AGENT'
AND l.project_id=9904
mysql> select * from salary_header;
+-----------+-----------+
| salary_id | status |
+-----------+-----------+
| 1 | APPROVING |
| 2 | PAID |
+-----------+-----------+
mysql> select * from salary_lines;
+----------------+-----------+------------+--------+
| salary_line_id | salary_id | project_id | salary |
+----------------+-----------+------------+--------+
| 1 | 2 | 9905 | 200.00 |
+----------------+-----------+------------+--------+
mysql> select * from salary_pay_headers;
+---------------+----------+
| salary_pay_id | pay_type |
+---------------+----------+
| 1 | AGENT |
| 2 | OTHER |
+---------------+----------+
mysql> select * from salary_pay_lines;
+--------------------+---------------+------------+--------+
| salary_pay_line_id | salary_pay_id | project_id | salary |
+--------------------+---------------+------------+--------+
| 1 | 1 | 9904 | 3.05 |
| 2 | 1 | 9904 | 201.37 |
| 3 | 1 | 9904 | 6.10 |
| 4 | 1 | 9904 | 10.17 |
| 5 | 1 | 9904 | 6.44 |
| 6 | 1 | 9904 | 9.15 |
| 8 | 3 | 9905 | 100.00 |
+--------------------+---------------+------------+--------+
Its result is not 3.05+201.37+6.10+10.17+6.44+9.15=236.28 as I expected，but 236.28+200=436.28，obviously that one in the salary_line is not filtered out. I have spent the whole afternoon on this problem, so I really want to know the execution order of this SQL.
