SQL LEFT JOIN: unbekanntes Tabellenfeld xxx in on clause

[WulfmanGER]

Hallo geschätzte mySQL-Fans

ich komme einfach nicht weiter

Ich habe 3 Tabellen (unter mySQL 5.7) die verknüpft werden müssen_
cm_events, locations, category

Jetzt hab ich das Problem das in cm_events 2 ID-Spalten enthalten sind die auf locations (id) verweisen.
cm_events: ... | locationID | ticketLocationID | ...
locations: id | location (name)

Jetzt möchte ich eine Ausgabe:

locationID | location | ticketLocationID | tlocation | ... (insgesamt 15 Spalten; wobei ich hier noch am optimieren bin)

Folgendes Query hab ich jetzt

SELECT e.id, e.startDate, e.startTime, e.endDate, e.endTime, e.categoryID, c.catName, e.description, e.comment, e.class, e.URL, e.locationID, l.location AS loc, e.ticketLocationID, l.location AS tlocation, e.ticket
    FROM cm_events e, category c, locations l
        LEFT JOIN
            ( SELECT id, location AS ljloc FROM locations ) lj
            ON lj.id = e.ticketLocationID
    WHERE c.mainCatID = 1
    AND c.id = e.categoryID
    AND e.locationID = l.id
    AND e.ticketLocationID = l.id
    AND e.categoryID IN(1,2,3,4,5,6,7)

Fehlermeldung: #1054 - Unbekanntes Tabellenfeld 'e.ticketLocationID' in on clause

Ich versteh es einfach nicht :/ Ich hab jetzt zig Seiten durch ... auch einen SQL-Validator ... ich finde den Fehler nicht. Viele Seiten mit ähnlichen Aufbau gesehen; SELECT, FROM, LEFT JOIN - ON, WHERE, AND. z.B. https://stackoverflow.com/questions/9973159/mysql-left-join-where-table2-field-x (die vorletzte Antwort)

Ich hab testweise mal das WHERE und folgend entfernt - immer noch. Wenn ich den LEFT JOIN komplett entferne -> klappt (natürlich nicht das gewünschte Ergebnis) - was ja auch dafür spricht das ticketLocationID existiert bzw. oben auch richtig angesprochen wird. Der Inhalt des JOINs klappt natürlich auch . Ebenfalls folgendes probiert LEFT JOIN (SELECT...) durch: LEFT JOIN locations lj ON lj.id = e.ticketLocationID - ersetzt: gleicher Fehler (dürfte aber, wenn korrekt: nicht mein Wunschergebnis geben?!)

Wenn ich e.ticketLocationID durch lj.id ersetze (was KEINEN Sinn macht), kommt keine Meldung. Übersehe ich hier etwas?
Danke schon mal...

 

[wahli]

Die Query in der Klammer muss für sich alleine funktionieren.

Du möchtest nur aus cm_events Daten ausgeben, somit brauchst du bei "from" nur diese Tabelle angeben:
"FROM cm_events e"
Danach machst du die Joins auf die anderen Tabellen.
Edit: Sorry, ich hätte nach rechts scrollen sollen
Trotzdem solltest du die Joins anders organisieren. Die Query in der Klammer muss für sich selbst funktionieren, deshalb bekommst du den Fehler.

 

[TrueAzrael]

Bin kein SQL-Guru, aber wäre das einfachste nicht:

SELECT
*

FROM
cm_events e
LEFT JOIN category c on c.id = e.categoryID
LEFT JOIN location loc on loc.id = e.locationID
LEFT JOIN location tloc on tloc.id = e.ticketLocationID

WHERE

Oder so ähnlich? Nimmst dir die Event-Tabelle joinst die Category-Tabelle dazu und dann zwei mal die Location-Tabelle nur halt einmal mit locationID und einmal mit ticketLocationID.

Vermutlich nicht die sauberste Lösung sollte aber klappen.

Edit: Es hilft übrigens (zumindest mir) wenn man mit 'select *' und ohne where startet und sich mal ansieht was der join so liefert. Natürlich nur wenn keine Gefahr besteht mit dem query einen relevanten Server in die Knie zu zwingen, aber für gewöhnlich entwickelt man die Sachen eh nicht am Produktionsgerät...

 

[sgrisius]

Sowas in der Art müsste es sein

select *
from cm_events e inner join category c on c.id = e.categoryID
location l inner join location l on e.locationID = l.id
location lj left outer join on lj.id = e.ticketLocationID
WHERE c.mainCatID = 1
AND e.categoryID IN(1,2,3,4,5,6,7)

Ergänzung (31. Oktober 2019)

Bin kein SQL-Guru, aber wäre das einfachste nicht:

SELECT
*

FROM
cm_events e
LEFT JOIN category c on c.id = e.categoryID
LEFT JOIN location loc on loc.id = e.locationID
LEFT JOIN location tloc on tloc.id = e.ticketLocationID

WHERE

Oder so ähnlich? Nimmst dir die Event-Tabelle joinst die Category-Tabelle dazu und dann zwei mal die Location-Tabelle nur halt einmal mit locationID und einmal mit ticketLocationID.

Vermutlich nicht die sauberste Lösung sollte aber klappen.

Genau so, du musst location 2x benutzen und ihm einen anderen Namen geben (hier loc & tloc) und dann deinen join darauf aufbauen

 

[Anxi]

Das Kreuzprodukt ist halt auch echt kacke:

FROM cm_events e, category c, locations l

würde ich mit vernünftigen Joins realisieren und dann siehste recht fix wo der Fehler ist.

€: Da war TrueAzrael halt schneller xD

 

[WulfmanGER]

DANKE EUCH.

So sieht es jetzt aus

SELECT *
FROM cm_events e
LEFT JOIN category c on c.id = e.categoryID
LEFT JOIN locations loc on loc.id = e.locationID
LEFT JOIN locations tloc on tloc.id = e.ticketLocationID

WHERE e.locationID = loc.id 
    AND e.categoryID IN(1,2,3,4,5,6,7)

Sieht so aus als ob es klappt. Ich muss die Ergebnismasse (SELECT *) jetzt erstmal kürzen, einschränken um besseren Überblick zu bekommen - meine Testdaten sind auch irgendwie nicht so optimal ... Wald vor lauter Bäumen ...

Mir war das mit dem FROM x, y, z nicht klar das man das so nicht darf/kann (in kombi mit Joins). Muss echt mal mehr mit JOINS arbeiten - das ist für mich immer noch ein Minenfeld Also im Prinzip lege ich hier 3(+1) Tabellen nebeneinander: zuerst die cm_events - daneben lege ich die category (categoryID) und daneben lege ich 2x die locations (anhand der zwei LocationIDs in cm_events). Klingt einfach

 

[Anxi]

Der Vorteil von sauberen Joins und sauberen Selects ist vor allem die Geschwindigkeit bzw. die erfasste Datenmenge.
In deinem ersten Statement wurden, lapidar gesagt, einfach mal 3 Tabellen miteinander multipilizert. Je nach Größe kann das recht schnell eine beachtliche rechenoperation geben.
Warum gehst du mit LEFT JOINs auf Nummer sicher?

 

[TrueAzrael]

@Wulfman_SG

Wenn du mit

WHERE e.locationID = loc.id

sowieso nur die Einträge ausgibst wo ein Location-Eintrag für die locationID vorhanden ist, könntest du den entsprechenden JOIN auch auf INNER wechseln.
Keine Ahnung was weniger Rechenaufwand ist. Vermute aber, dass die Variante mit INNER JOIN die bessere ist.
Es wird wohl zuerst gejoint und dann die WHERE Prüfung durchgeführt, wenn der JOIN schon weniger Datensätze liefert dürfte das daher flotter sein.

 

[mental.dIseASe]

Vor allem würde ich auch als Maintainer erwarten, solche Join-Bedingungen in den Joins zu finden und nicht in der WHERE-Klausel.

 

[parats]

Mal ne generelle Frage - wieso left joins?
Vom ersten schätzen ist Event eine Faktentabelle und Location/Category sind Dimensionstabellen, korrekt?
Wieso sollte es Einträge in der Faktentabelle für einen Dimensionswert geben, der in der Dimension fehlt?
Ich würde dir empfehlen grundsätzlich mit defaults zu arbeiten und einen "unbekannt" Eintrag in der Dimension mit Referenz auf die Faktentabelle zu erstellen. Bspw eignet sich die -1 als Eintrag für eine ID mit einem <N/A> als Bezeichnung.

Ich sehe immer wie Leute hier wild mit left und right joins um sich werfen und das nur, weil man sich beim handling der Daten keine oder nur wenig Gedanken gemacht hat.
PK/FK und Referentielle Integrität sind wirklich die Basis..

Davon mal abgesehen ist ein inner join deutlich schneller.

 

[WulfmanGER]

Moin,

wie ich ja angedeutet habe - so recht Ahnung hab ich hier leider nicht.

Ich hab jetzt mal INNER versucht - das bringt mir aber nicht das gewünschte Ergebnis.

Ich bekomme auch die WHERE's nicht weg ...

SELECT e.id, e.startDate, e.startTime, e.description, e.URL, e.locationID, loc.location, e.ticketLocationID, tloc.location AS tlocation
FROM cm_events e
LEFT JOIN category c on c.id = e.categoryID
LEFT JOIN locations loc on loc.id = e.locationID
LEFT JOIN locations tloc on tloc.id = e.ticketLocationID
WHERE e.categoryID IN(1,2,5,6,7,3,4)  
    AND c.mainCatID =1
ORDER BY e.startDate ASC, e.startTime ASC

wobei:
e.categoryID IN(1,2,5,6,7,3,4)
c.mainCatID =1
Im moment der "vollausbau" sind ... meine "dringend" Variante benötigt nur CategoryID 3,4

Wenn ich jetzt mit "vollausbau" die LEFTs durch INNER ändere, bekomme ich nur ein Bruchteil der erwarteten Ergebnissmenge... auch die beiden WHERES kann ich nicht verschieben. Zumindest weiß ich nicht wohin.

Das query oben ist bisher die beste Lösung (muss aber bei der Ausgabe noch erweitert werden...). Wobei meine Testdaten noch etwas unübersichtlich sind ... sieht alles passend aus - hoffe das bleibt

 

[parats]

Dann hast du Einträge in der Tabelle cm_events für die Tabelle Category oder Location. Mein Tipp ist Location weil dein where clause einen inner join impliziert. Schau doch welche Locations fehlen und vor allem warum das der Fall ist. Gruselig sowas..