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:
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…