I have 2 tables companies
and invoices
I want to select all companies with their most recent invoice price.
I don’t seem to get it working.
This is what I tried:
x
SELECT *
FROM companies H INNER JOIN
invoices V
ON H.company_id = V.BC_ID
WHERE V.ISCOMMISSIE = 0 AND
V.DATE = (SELECT MAX(v2.DATE) FROM invoices v2 WHERE v2.BC_ID = V.BC_ID AND v2.ISCOMMISSIE = 0);
But the query loads very long and I don’t know why. The structure looks like this:
companies
company_id | company_name |
1 | company 1 |
2 | company 2 |
invoices
invoice_id | BC_ID | DATE | ISCOMMISSIE | price |
1 | 2 | 2020-01-01 | 0 | 340,40 |
2 | 1 | 2020-01-11 | 0 | 240,40 |
3 | 1 | 2020-01-08 | 0 | 250,30 |
4 | 2 | 2020-01-18 | 0 | 150,30 |
5 | 2 | 2020-01-19 | 1 | 150,30 |
The BC_ID
is the same as the company_id
and ISCOMMISSIE
should be 0.
I want to select the most recent date.
Does someone have an idea on how to do this and also make the query as fast as possible?
http://sqlfiddle.com/#!9/2fc3a/1
Advertisement
Answer
Try:
SELECT H.*, V.*
FROM companies H
INNER JOIN invoices V ON H.company_id = V.BC_ID
INNER JOIN ( SELECT v2.BC_ID, MAX(v2.DATE) DATE
FROM invoices v2
WHERE v2.ISCOMMISSIE = 0
GROUP BY v2.BC_ID ) v3 ON v.BC_ID = v3.BC_ID
AND v.DATE = v3.DATE
AND V.ISCOMMISSIE = 0
And the index invoices (ISCOMMISSIE, BC_ID, DATE)
may help…