MySQL JOIN: Ausgabe von Feld aus Tabelle 2 nach Anfrage für Tabe

[Hubble]

Ich habe ein Problem mit einer MySQL-Anfrage. Kurzfassung der Tabellen (haben noch viel mehr Spalten...):

tab1:
titel1
datum

tab2:
titel2
autor

Ich habe eine funktionierende Anfrage für T1 mit WHERE Bedingungen:

SELECT  
titel1, datum  
FROM tab1  
WHERE titel1 LIKE irgendwas  
ORDER BY irgendwas DESC LIMIT 0,50

Nun möchte aber gerne noch den jeweiligen autor zum titel und datum mit ausgeben. titel1 und titel2 sind identisch, ein ON / WHERE titel1 = titel2 ist also möglich. Ich bekomme es aber nicht hin aus T2 nur den autor zu selecten ohne die 20 anderen Felder einbinden zu müssen

Bin kein totaler Anfänger, aber irgendwie blicke ich mit den JOINs noch nicht ganz durch.

Wie kann ich also autor aus tab2 WHERE title1 = title1 in die gleiche Anfrage einbinden?

Dank vorab für die Vorschläge.

[EKKi]

Mahlzeit Hubble,

Wie kann ich also autor aus tab2 WHERE title1 = title1 in die gleiche Anfrage einbinden?

Indem Du Deine Abfrage

SELECT

titel1, datum
FROM tab1
WHERE titel1 LIKE irgendwas
ORDER BY irgendwas DESC LIMIT 0,50

  
an ungefähr wie folgt änderst:  
  
~~~sql
SELECT t1.titel1  
,      t1.datum  
,      t2.autor  
  FROM tab1      t1  
  LEFT JOIN tab2 t2 ON t1.title1 = t2.title1

Bin kein totaler Anfänger, aber irgendwie blicke ich mit den JOINs noch nicht ganz durch.

Na, dann würde ich Dir doch mal die Lektüre des Artikels "Einführung in Joins" ans Herz legen wollen.

MfG,
EKKi

[Hubble]

Hi EKKi,

danke für die Hilfestellung!
Die Einführung hier liest sich in der Tat angenhemer als die offiziellen MySQL Docs.
Ok das Ergebnis von dem obigen hatte ichs chon, nur jetzt ist der Code sauberer. In der Ausgabe sind die Zuordnungen aus t2 richtig, aber die einzelnen titel sind mehrfach gelistet (zweichfach bis vierfach) alles innerhalb des LIMITS.
Na ich les' erstmal, vielleicht findet sich ja die Lösung dort.

Mahlzeit Hubble,

Wie kann ich also autor aus tab2 WHERE title1 = title1 in die gleiche Anfrage einbinden?

Indem Du Deine Abfrage

SELECT

titel1, datum
FROM tab1
WHERE titel1 LIKE irgendwas
ORDER BY irgendwas DESC LIMIT 0,50

>   
> an ungefähr wie folgt änderst:  
>   
> ~~~sql

SELECT t1.titel1  

> ,      t1.datum  
> ,      t2.autor  
>   FROM tab1      t1  
>   LEFT JOIN tab2 t2 ON t1.title1 = t2.title1

Bin kein totaler Anfänger, aber irgendwie blicke ich mit den JOINs noch nicht ganz durch.

Na, dann würde ich Dir doch mal die Lektüre des Artikels "Einführung in Joins" ans Herz legen wollen.

MfG,
EKKi

[Hubble]

Ok, es ist doch verzwickter.
Bis zur Mehrfachausgabe bin ich ja schon gekommen. Jetzt weiß ich wenigstens warum: title ist in t2 mehrfach vorhanden, sorry.

ON t1.page_title = t2.rc_title gibt also t2.rc_title so oft aus wie es vorhanden ist.

Ich bäuchte aber t2.title mit der kleinsten t2.id oder etwas in der Art
ON (t1.page_title = t2.rc_title ORDER BY t2.id LIMIT 0,1)
was aber nicht funktioniert, da danach die WHERE, ORDER UND LIMIT für t1 kommen.

[Vinzenz Mai]

Hallo,

Ok, es ist doch verzwickter.
Bis zur Mehrfachausgabe bin ich ja schon gekommen. Jetzt weiß ich wenigstens warum: title ist in t2 mehrfach vorhanden, sorry.

Ich bäuchte aber t2.title mit der kleinsten t2.id

nun ja, abgesehen davon, dass einer id normalerweise keine andere Bedeutung zukommt als einen Datensatz eindeutig zu identifizieren, suchst Du korrelierte Unterabfragen.

Freundliche Grüße

Vinzenz

[Hubble]

Ok, verschachteln :)

    FROM page t1 LEFT JOIN recentchanges t2  
      ON t1.page_title = (SELECT t1.page_title, t2.rc_title  
                          FROM page t1 LEFT JOIN recentchanges t2  
                          ON t1.page_title = t2.rc_title  
                          ORDER BY t2.rc_id  
                          LIMIT 1)

Das haut nicht hin, weil er mir t1.page_title und t2.rc_title ausgibt.

Ginge es nicht irgendwie einfacher mit min(t2.rc_id)?

Hier mal der komplette Code, noch mit Mehrfachausgabe:

  
SELECT  
  t1.page_namespace, t1.page_title, t1.page_is_redirect,  
  t2.rc_title, t2.rc_timestamp, t2.rc_user, t2.rc_user_text  
FROM page t1 LEFT JOIN recentchanges t2  
  ON t1.page_title = t2.rc_title  
WHERE t1.page_is_redirect = 0  
  AND t1.page_namespace = 0  
  AND (t1.page_title LIKE '%".$thismonth."%' OR t1.page_title LIKE '%".$lastmonth."%')  
ORDER BY t1.page_id DESC  
LIMIT 0,{$this->limit}" );  

[Vinzenz Mai]

Hallo,

Ok, verschachteln :)

Ja, Subselects, aber nichts mit ...

FROM page t1 LEFT JOIN recentchanges t2

ON t1.page_title = (SELECT t1.page_title, t2.rc_title
                          FROM page t1 LEFT JOIN recentchanges t2
                          ON t1.page_title = t2.rc_title
                          ORDER BY t2.rc_id
                          LIMIT 1)

  
irgendwelchem ORDER BY, LIMIT und ähnlichen Krams, weil ...  
  

> Das haut nicht hin, weil er mir t1.page\_title und t2.rc\_title ausgibt.  
  
... as nicht hinhaut. Vor allem dann nicht, wenn einem das DBMS Fehlermeldungen wegen nicht gruppierten Spalten um die Ohren haut (ok, MySQL stört sich nicht dran, liefert planlos irgendetwas aus).  
  

> Ginge es nicht irgendwie einfacher mit `min(t2.rc_id)`{:.language-sql}?  
  
Einfacher, das ist die Frage. Es geht mit MIN(), MAX() oder ähnlichem, mit einer korrelierten Unterabfrage, wie von mir bereits verlinkt.  
  

> ~~~sql
  

> SELECT  
>   t1.page_namespace, t1.page_title, t1.page_is_redirect,  
>   t2.rc_title, t2.rc_timestamp, t2.rc_user, t2.rc_user_text  
> FROM page t1 LEFT JOIN recentchanges t2  
>   ON t1.page_title = t2.rc_title  
> WHERE t1.page_is_redirect = 0  
>   AND t1.page_namespace = 0  
>   AND (t1.page_title LIKE '%".$thismonth."%' OR t1.page_title LIKE '%".$lastmonth."%')  
> ORDER BY t1.page_id DESC  
> LIMIT 0,{$this->limit}" );  
> 

Ist im Titel irgendwie die Monatsbezeichnung integriert? Nutze dafür lieber Datums- und Zeitfunktionen. Das ist cleverer.

Freundliche Grüße

Vinzenz

[Hubble]

Ist im Titel irgendwie die Monatsbezeichnung integriert?

Ja alle Titel fangen mit YYYY-MM-DD an, gefolgt von Text. Kann aber auch mal 200X sein oder so.

Nutze dafür lieber Datums- und Zeitfunktionen. Das ist cleverer.

Tue ich (wenn ich dich richtig verstanden habe). $thismonth und $lastmonth sind php date("Y-m") und das gleiche -1.

Einfacher, das ist die Frage. Es geht mit MIN(), MAX() oder ähnlichem, mit einer korrelierten Unterabfrage, wie von mir bereits verlinkt.

Tja nur wie einbinden? t2.rc_title soll nur einmalig im ON ausgegeben werden und zwar von der Zeile mit min(t2.rc_id).

[Vinzenz Mai]

Hallo,

Ist im Titel irgendwie die Monatsbezeichnung integriert?
Ja alle Titel fangen mit YYYY-MM-DD an, gefolgt von Text. Kann aber auch mal 200X sein oder so.

Nutze dafür lieber Datums- und Zeitfunktionen. Das ist cleverer.
Tue ich (wenn ich dich richtig verstanden habe). $thismonth und $lastmonth sind php date("Y-m") und das gleiche -1.

Du hast mich nicht verstanden. Speichere das Datum lieber in einer Spalte vom Datentyp DATE ab und selektiere nach dieser Datumsspalte.

Einfacher, das ist die Frage. Es geht mit MIN(), MAX() oder ähnlichem, mit einer korrelierten Unterabfrage, wie von mir bereits verlinkt.
Tja nur wie einbinden? t2.rc_title soll nur einmalig im ON ausgegeben werden und zwar von der Zeile mit min(t2.rc_id).

Und wo bist Du genau hängengeblieben? Für genau solche Aufgaben nutzt man korrelierte Unterabfragen. Ok, ich versuch's Dir Schritt-für-Schritt zu erklären (ohne Deine WHERE-Klausel, ORDER BY und LIMIT, die sich ja nicht ändern):

1. Wir schauen, was aus der Tabelle recentchanges benötigt wird, siehe Dein eigenes Statement:

» SELECT  

>   t1.page_namespace, t1.page_title, t1.page_is_redirect,  
>   t2.rc_title, t2.rc_timestamp, t2.rc_user, t2.rc_user_text  
> FROM page t1 LEFT JOIN recentchanges t2  
>   ON t1.page_title = t2.rc_title  

Du benötigst
  - rc_title
  - rc_timestamp
  - rc_user
  - rc_user_text,

das führt zunächst (mit Mehrfachnennungen) zu

SELECT  
    rc_title,      -- ich halte nichts von Tabellenpräfixen in Spaltennamen.  
    rc_timestamp,  
    rc_user,  
    rc_user_text  
FROM  
    recentchanges  

Nun möchtest Du bei gleichem Titel nur diejenigen, die den kleinsten id-Wert (rc_id) aufweisen, wir nutzen eine korrelierte Unterabfrage:

  
SELECT                           -- Gib mir  
    rc_title,                    -- die gewünschten Spalten  
    rc_timestamp,  
    rc_user,  
    rc_user_text,  
    rc_id                        -- (damit Du die Richtigkeit prüfen kannst)  
FROM                             -- aus meiner Tabelle, die wir mit einem  
    recentchanges rc1            -- Alias ansprechen, um sie von der  
                                 -- Verwendung im Subselect zu unterscheiden  
WHERE                            -- wobei nur die Datensätze gewünscht sind  
    rc1.rc_id = (                -- bei denen die id gleich  
        SELECT                   -- der  
            MIN(rc2.rc_id)       -- kleinsten id  
        FROM                     -- aus  
            recentchanges rc2    -- deiner Tabelle  
        WHERE                            -- wobei innere und äußere Abfrage  
            rc2.rc_title = rc1.rc_title  -- über gleichen Titelnamen in  
    )                            -- Beziehung zueinander stehen (korreliert sind)  

2. Im zweiten Schritt joinst Du einfach Deine Seiten-Tabelle mit dieser Abfrage. Join-Spalte ist die Titel-Spalte:

SELECT  
    p.page_namespace,  
    p.page_title,  
    p.page_is_redirect,  
    -- rc.rc_title       -- wird nicht benötigt  
    rc.rc_timestamp,  
    rc.user,  
    rc_user_text  
FROM  
    page p               -- p ist sinnvoller als ein aussageloses t1  
LEFT OUTER JOIN (        -- Join-Partner ist die korrelierte Unterabfrage  
    SELECT  
        rc_title,  
        rc_timestamp,  
        rc_user,  
        rc_user_text     -- (hier ohne die überflüssige id-Spalte)  
    FROM  
        recentchanges rc1  
    WHERE  
        rc1.rc_id = (  
            SELECT  
                MIN(rc2.rc_id)  
            FROM  
                recentchanges rc2  
            WHERE  
                rc2.rc_title = rc1.rc_title  
        )  
    ) rc                 -- die zwingend einen Aliasnamen benötigt,  
                         -- damit Du auf die Spalten zugreifen kannst.  

Anmerkungen:

- t1, t2 sind im Allgemeinen verbesserungswürdige Aliasnamen.
  - bei einem korrelierten Subselect dürfen die zwei Namen dagegen gerne
    mit einer Ziffer unterschieden werden.
  - Eine id sollte man nicht mit zusätzlicher Information ausstatten, nutze
    für den ältesten (oder neuesten) Eintrag Zeitinformationen, zum Beispiel
    eine DATETIME oder (MySQL-)TIMESTAMP-Spalte.
  - wie bereits angemerkt, halte ich Spaltenpräfixe aus dem Tabellennamen
    für keine besonders gute Idee.
  - Die Datumsinformation für Deine WHERE-Klausel bringe (zusätzlich zum
    Titel) in einer DATE-Spalte unter.

Freundliche Grüße

Vinzenz

[Hubble]

Vielen Dank für die ausführliche Beschreibung! :)
Ich hatte allerdings scheinbar unüberwindbare Syntaxfehler ab allem nach  ) rc

  
SELECT  
    p.page_namespace,  
    p.page_title,  
    p.page_is_redirect,  
    rc.rc_timestamp,  
    rc.rc_user,  
    rc.rc_user_text  
FROM  
    page p  
LEFT OUTER JOIN (  
    SELECT  
        rc_title,  
        rc_timestamp,  
        rc_user,  
        rc_user_text  
    FROM  
        recentchanges rc1  
    WHERE  
        rc1.rc_id = (  
            SELECT  
                MIN(rc2.rc_id)  
            FROM  
                recentchanges rc2  
            WHERE  
                rc2.rc_title = rc1.rc_title  
        )  
    ) rc  
WHERE page_is_redirect = 0 ...  

Egal ob ich nun mit zB page_is_redirect oder p.page_is_redirect oder sogar rc.page_is_redirect fortfahre.

Generell kann und will ich die Tabellenstruktur nicht ändern. Es handelt sich um ein Wiki mit bereits 17.000 Artikeln.
Die Präfixe sind doch in Ordnung? Die Mediawiki Jungs haben sich sicher was dabei gedacht. title, user etc tauchen in vielen der knapp 50 Tabellen auf :) Zur Identifizierung also ganz hilfreich.

Du hast mich nicht verstanden. Speichere das Datum lieber in einer Spalte vom Datentyp DATE ab und selektiere nach dieser Datumsspalte.

Du meinst mit Datum doch mein ~~~sql WHERE ... AND
(t1.page_title LIKE '%".$thismonth."%' OR t1.page_title LIKE '%".$lastmonth."%')

Das macht schon Sinn. Dass alle Titel mit YYYY-MM-DD anfangen hat aber mit der Query hier nichts zu tun. Stell dir vor die Titel lauten alle in der Art "2009-01-17 - Rolling Stones live in Hamburg". Diese Datumsangabe am Anfang ist keine solche. Sie hat mit dem rc\_timestamp (letztes Änderungsdatum) oder der rc\_id (ID der jeweiligen Änderung) nichts zu tun.  
Die Anfrage dient einer Liste der letzten 50 ($this->limit) Artikel (aus Tabelle page) deren Inhalt immer (auch in Zukunft) aus den letzten 2 Monaten stammt (YYYY-MM-DD am Anfang), was das "Titel like lastmonth (z.Z. = 2009-01) or thismonth (z.Z. = 2009-02)" gewährleistet und neue Artikel, die sich zB um 1999 drehen, ausfiltert.  
  
Was ist mit nachfolgender Variation? Sie ergibt zwar keine Syntax fehler, beinhaltet aber noch immer die title-Doppelungen aus recentchanges.  
~~~sql
  
SELECT  
    t1.page_namespace,  
    t1.page_title,  
    t1.page_is_redirect,  
    t2.rc_timestamp,  
    t2.rc_user,  
    t2.rc_user_text  
FROM page AS t1 INNER JOIN recentchanges AS t2  
    ON t1.page_title = t2.rc_title  
       AND t2.rc_id IN (  
        SELECT mySub.id FROM  
        (  
            SELECT  
                rc_timestamp, rc_title, rc_user, rc_user_text, MIN(rc_id) AS id  
            FROM  
                recentchanges AS t3  
            GROUP BY  
                rc_timestamp, rc_title, rc_user, rc_user_text  
        ) AS mySub  
    )  

[Vinzenz Mai]

Hallo,

Ich hatte allerdings scheinbar unüberwindbare Syntaxfehler ab allem nach  ) rc

wegen der d***lichen Präfixe hatte ich ein paar Fehler eingebaut.

WHERE page_is_redirect = 0 ...
[/code]
Egal ob ich nun mit zB page_is_redirect oder p.page_is_redirect oder sogar rc.page_is_redirect fortfahre.

welche Fehlermeldung? Ich hab' gerade keine Glaskugel zur Hand. Ein paar Aliasnamen fehlten noch; versieh *jede* Spalte mit einem qualifizierten Namen.

Wenn das SELECT ohne WHERE-Klausel abgearbeitet wird (prüfe in phpMyAdmin oder einem anderen Client), dann wird es auch mit WHERE-Klausel abgearbeitet.

Generell kann und will ich die Tabellenstruktur nicht ändern. Es handelt sich um ein Wiki mit bereits 17.000 Artikeln.
Die Präfixe sind doch in Ordnung? Die Mediawiki Jungs haben sich sicher was dabei gedacht.

es ist meine persönliche Meinung - mit der ich bis jetzt hervorragend gefahren bin. *Ich* mache bei Präfixen verdammt viele Fehler, weil ich die Statements nicht mehr lesen kann.

Du meinst mit Datum doch mein ~~~sql

WHERE ... AND

(t1.page_title LIKE '%".$thismonth."%' OR t1.page_title LIKE '%".$lastmonth."%')

> Das macht schon Sinn. Dass alle Titel mit YYYY-MM-DD anfangen hat aber mit der Query hier nichts zu tun. Stell dir vor die Titel lauten alle in der Art "2009-01-17 - Rolling Stones live in Hamburg". Diese Datumsangabe am Anfang ist keine solche. Sie hat mit dem rc\_timestamp (letztes Änderungsdatum) oder der rc\_id (ID der jeweiligen Änderung) nichts zu tun.  
  
Nun ja, ohne jegliche Beispieldaten kann ich mir darunter eben nichts sinnvolles vorstellen.  
  

> Die Anfrage dient einer Liste der letzten 50 ($this->limit) Artikel (aus Tabelle page) deren Inhalt immer (auch in Zukunft) aus den letzten 2 Monaten stammt (YYYY-MM-DD am Anfang), was das "Titel like lastmonth (z.Z. = 2009-01) or thismonth (z.Z. = 2009-02)" gewährleistet und neue Artikel, die sich zB um 1999 drehen, ausfiltert.  
  
äh ja, sowas geht auch mit SQL. Ich ziehe das vor.  
  

> Was ist mit nachfolgender Variation? Sie ergibt zwar keine Syntax fehler, beinhaltet aber noch immer die title-Doppelungen aus recentchanges.  
  
Unbrauchbar.  
  
  
  
Freundliche Grüße  
  
Vinzenz

[Hubble]

wegen der d***lichen Präfixe hatte ich ein paar Fehler eingebaut.

Habe ich bemerkt, korrigiert und gepostet. Für die Präfixe kann ich nichts. Werde sicher nicht den ganzen Tabellenaufbau und Wiki-Code für eine kleine Listenausgabe mit schöner Query ändern. Ist mir neu dass sinnvolle Präfixe Probleme verursachen.

welche Fehlermeldung? Ich hab' gerade keine Glaskugel zur Hand.

Syntaxfehler halt.
1064: You have an error ... near 'WHERE page_is_redirect = 0 AND page_namespace = 0 AND (page_title ' at line 27

Ein paar Aliasnamen fehlten noch;

Danke, muss ich mal schauen.

versieh *jede* Spalte mit einem qualifizierten Namen.

s. oben

[Hubble]

Hier kannst du die Welt verbessern: http://www.mediawiki.org/wiki/Bugzilla

[Vinzenz Mai]

Hallo,

Vielen Dank für die ausführliche Beschreibung! :)
Ich hatte allerdings scheinbar unüberwindbare Syntaxfehler ab allem nach  ) rc

traue nie fertigem Code. Neben ein paar fehlenden Präfixen ...

  

> SELECT  
>     p.page_namespace,  
>     p.page_title,  
>     p.page_is_redirect,  
>     rc.rc_timestamp,  
>     rc.rc_user,  
>     rc.rc_user_text  
> FROM  
>     page p  
> LEFT OUTER JOIN (  
>     SELECT  
>         rc_title,  
>         rc_timestamp,  
>         rc_user,  
>         rc_user_text  
>     FROM  
>         recentchanges rc1  
>     WHERE  
>         rc1.rc_id = (  
>             SELECT  
>                 MIN(rc2.rc_id)  
>             FROM  
>                 recentchanges rc2  
>             WHERE  
>                 rc2.rc_title = rc1.rc_title  
>         )  
>     ) rc  
  
-- ... hab ich hier die ON-Klausel des Joins vergessen  
ON  
    p.page_title = rc.rc_title  
-- Ende der Vergesslichkeit, ab hier kommen Deine Fehler :-)  
  

> WHERE page_is_redirect = 0 ...  

Freundliche Grüße

Vinzenz

[Hubble]

ok super. kann ich ja vielleicht bald schlafen gehen. F5 kann ich morgen ölen.

[Hubble]

Ok das fehlende ON war's :) Wäre mir erst nach weiteren 3 Stunden aufgefallen.

Klappt auch mit dreckigen Präfixen. Die Speicherung des "Datums" in ein extra Feld ist wirklich den Aufwand nicht wert. Diese Anfrage ist das erste Mal dass ich mit date() rumwurschteln muss in einer Anfrage. Wenn du Wikis kennst weißt du dass sie Artikel generell nur aus zwei Inputfeldern bestehen.