Join multiple tables with "GROUP BY" and last date in MySQL
I have 2 MySQL tables
Table 1 is "qualitaet -ventar" and has 3000 rows with "sachnummer" as AUTO_INCREMENT
table 2 is "qualitaet" and has multiple rows .. here "id" is set to AUTO_INCREMENT the important thing is that the "sugar number" can exist many times. (see red marks in the picture)
what i want to do is left join from "qualitaet" to "qualitaet -ventar"
i want to group all "sachnummer" with the most recent "created" date on "qualitaet"
and when "sachnummer" is not available in "qualitaet" make an empty join with "qualitaet -ventar"
so i have this code
SELECT
i.'sachnummer' AS id,
MAX(q.'created') AS letztemessung,
i.'sachnummer-name' AS sachnummer
FROM
'qualitaet-inventar' i
LEFT JOIN
'qualitaet' q on i.'sachnummer' = q.'sachnummer'
GROUP BY
sachnummer
ORDER BY
sachnummer ASC
the problem is that it takes a long time to get this result
with this code it runs faster
SELECT
q.'sachnummer' AS id,
MAX(q.'created') AS letztemessung,
i.'sachnummer-name' AS sachnummer
FROM
qualitaet q
LEFT JOIN
'qualitaet-inventar' i on q.'sachnummer' = i.'sachnummer'
GROUP BY
sachnummer
ORDER BY
sachnummer ASC
but with this code i cant get hole info and empty date
Is it possible to get this table faster for tall table rows?: D
Editing:
here are my indices
source to share
The following shouldn't run faster than your existing query, but from your description it might:
SELECT i.`sachnummer` AS id,
MAX(q.`created`) AS letztemessung,
i.`sachnummer-name` AS sachnummer
FROM `qualitaet-inventar` i
JOIN `qualitaet` q on i.`sachnummer` = q.`sachnummer`
GROUP BY sachnummer
UNION ALL
SELECT i.`sachnummer` AS id,
q.`created` AS letztemessung,
i.`sachnummer-name` AS sachnummer
FROM `qualitaet-inventar` i
LEFT JOIN `qualitaet` q on i.`sachnummer` = q.`sachnummer`
WHERE q.`sachnummer` IS NULL
ORDER BY sachnummer ASC
Another possibility:
select id, max(letztemessung) as letztemessung, max(sachnummer) as sachnummer
from
(SELECT `sachnummer` AS id,
`created` AS letztemessung,
'' AS sachnummer
FROM `qualitaet` q
UNION ALL
SELECT `sachnummer` AS id,
cast(null as datetime) AS letztemessung,
`sachnummer-name` AS sachnummer
FROM `qualitaet-inventar` i
) sq
group by id
ORDER BY sachnummer ASC
This is a general idea with shorter table names.
select somefields
from table1 t1 left join table2 t2 on t1.something = t2.something
join (
select something, max(datetimefield) maxdt
from table1
where whatever
group by something
) t3 on t1.something = t3.something
and t1.datetimefield = maxdt
where whatever
source to share