MySQL - Abfrage über drei Tabellen mit GROUPBY?

[tfact129]

Guten Abend,
im nachfolgenden Bild seht ihr drei Tabellen.


Bei der Abfrage soll als Ergebnis jede Maschine (die ja mehrfach in tbl_protocol vorkommt) nur einmal mit dem jüngsten Datum und der condition angezeigt werden. Ich brings einfach nicht hin, dieses Ergebnis anzuzeigen. Kann da jemand helfen?

alfa	11.11.2019​	excellent
beta	27.09.2018​	bad
gamma	23.08.2019​	bad

​

 

[Zvoni]

getestet in SQLite:

SELECT t2.machine_name, t1.machine_id, min(t1.date) as datum, t3.condition from tbl_protocol as t1
inner join tbl_machine as t2
on t2.id=t1.machine_id
inner join tbl_condition as t3
on
t3.id=t1.condition
group by t1.machine_id

Keine Ahnung inwieweit man das für MySQL abändern muss

 

[Yaslaw]

@Zvoni:
1) Unsauber. Da hat es viele Spalten die nicht im GROUP BY sind.
2) Zudem solle t3.condition ja passend zum Datum sein
3) wenn, dann max() für das Jüngste Datum und nicht min() (Also da neueste..)

@tfact129
Mans sollte nie ein Feld date nennen. Das ist ein Name der von SQL selber verwendet wird

Eine kleine Anleitung um den Jüngsten Datensatz pro Guppe auszuwählen: [SQL] Aktuelle Einträge pro Gruppe auslesen [Yaslaw.Info]

Ich habe mich für Einfaches Subquery mit IN() entschieden.

select m.machine_name, p.`date`, c.condition
from
    tbl_machine m,
    tbl_protocol p,
    tbl_condition c
where
    m.machine_id = p.machine_id
    and p.condition = c.id
    and (p.machine_id, p.`date`) in (
        select machine_id, max(`date`) as max_date
        from tbl_machine
        group by machine_id
    )

 

[Zvoni]

Yaslaw,
@ 1) weiss ich. Ist unsauber, aber SQLite lässt das eben zu.
@ 2) Ist es doch. "... ON t3.id=t1.condition" --> t1 ist das Protocol ?!?!?
@ 3) min vs. max. Yo, es ist noch mitten in der Nacht für mich.....

 

[Yaslaw]

@ 2) Ja. Aber da auf die Kondition keine Aggregationsfunktion (sum, min, max etc.) angewendet wird, wird wegen 1) darauf gruppiert. Gemäss Beispiel soll jedoch die condition genommen werden, die dem aktuellen Thema pro Maschine entspricht

- Gruppieren nach Maschine
- Pro Gruppe das neuste Datum
- und die entsprechende Condition

 

[Zvoni]

Ach so.
Ja, ist richtig, aber wie gesagt: SQLite lässt es zu, zu gruppieren, ohne alle nicht aggregierten Felder in einem Group By zu haben.
Da ich exklusiv nur auf t1 (protocol), und dort auf die id für min(date) (bzw. müsste das max(date) sein) gruppiere, holt sich der inner join nur den jeweiligen Satz aus t2 und t3

 

[tfact129]

Hallo ihr beiden, danke schonmal für eure Mühe
Ich hab beide Abfragen mal ausprobiert mit folgendem Ergebnis
1. Abfrage von Zvoni -> Ergebnis korrekt




2. Abfrage von Yaslaw

 

[Zvoni]

Muss ich widersprechen:
Für Maschine Beta müsste aber gemäss deinen Beispieldaten aber "bad" rauskommen.
Du bekommst aber ein good

 

[tfact129]

Oops, stimmt, hatte ich noch nicht überprüft ...

 

[Yaslaw]

Ok,bei mir ist ein kleiner Fehler drin. Das Subquery muss natürlich auf die protocol-Tabelle zugreifen.

Ich rate allgemein davon ab, diese Schwachstelle in MySQL zu nutzen. Man sollte jedes Feld definieren was damit gemacht werden soll. 1) ist dann klar, was man genau will, 2) ist es kompatibler zu anständigen SQL-Datenbanken

Test: SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

select m.machine_name, p.`date`, c.condition
from tbl_machine m, tbl_protocol p, tbl_condition c
where
    m.machine_id = p.machine_id
    and p.condition = c.id
    and (p.machine_id, p.`date`) in (
        select machine_id, max(`date`) as max_date
        from tbl_protocol
        group by machine_id
    );

| machine_name |       date | condition |
|--------------|------------|-----------|
|         alfa | 2019-11-11 | excellent |
|        gamma | 2019-08-23 |       bad |
|         beta | 2018-09-27 |       bad |