CODEWITHSUNDEEP
mysqlsqlselectjoinleft-join
bernte
bernte

Reputation: 1184

join multiple tables with "GROUP BY" and latest date in MySQL

i have 2 mysql tables

table 1 is "qualitaet-inventar" and has 3000 rows with "sachnummer" as AUTO_INCREMENT


table 2 is "qualitaet" and has a couple of rows.. here is "id" set to AUTO_INCREMENT the important thing is that "sachnummer" can be exist many times. (see the red marks in picture)


what i want to do is a left join from "qualitaet" to "qualitaet-inventar"

i want to group all "sachnummer" by showing the latest date of "created" on "qualitaet"

and when a "sachnummer" isn't available in "qualitaet" do an empty join to "qualitaet-inventar"


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 needs a long time to get this result


with this code it works 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 i don't get the hole inventory and an empty date with this code


is it possible to get this table in a faster way for high table-rows? :D

edit:

here are my indexes

Upvotes: 1

Views: 10183

Answers (2)

user359040
user359040

Reputation:

The following shouldn't work any faster than your existing query - but from your description, may do:

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

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

This is the 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

Upvotes: 0

Related Questions