MySQL Abfrage Join/View über mehrere Tabellen

[MiamiNice]

Hallo,

ich habe Probleme bei einer MySQL Abfrage die scheinbar meinen Horizont übersteigt. Es geht um einen View bzw. Join über mehrere Tables in einer XT:Commerce Datenbank.

Kann mir hier jemand helfen? Am liebsten per PM.

Vielen Dank im voraus.

[Asaraki]

Post doch mal was du hast und was du willst, joins alleine sind ja kein Hexenwerk:-)

[MiamiNice]

Ich brauche für einen Kunden eine spezielle Aufstellung der Artikeldaten damit er sie bei Rakuten in Japan importieren kann.

Was ich letztlich brauche ist ein Export der ungefähr so ausschaut:

Artikelnummer, Bezeichnung in english, Kategoriename in englisch.

Eigentlich ganz einfach :redface:

Unser Shop ist mehrsprachig.

Die Datenbank schaut so aus (nur die relevanten Tabellen und Felder):

Table "products" = products_id (intere Artikelnummer), products_model (die "richtige" Artikelnummer)

Table "products_description" = products_id, language_id (von 1 - 5 wobei 1 englisch ist), products_name (hier stehen die Übersetzungen drin)

Table "products_to_categories" = products_id, categories_id (in dem Table steht welcher Artikel in welcher Kategorie angezeigt wird. Es ist möglich das ein Artikel in multiplen Kategorien ist)

Table "categories_description" = categories_id, language_id (wieder von 1-5, 1 ist englisch), categories_name (Übersetzung der Kategorien)

Als Ausgabe brauche ich:

products_model, products_description (englisch), categories_name (englisch)

Es scheitert eigentlich an allem da ich nicht so oft Abfragen in SQL baue und schon gar nicht in dieser Größe. Ich bin mir schon unsicher mit welchem Table ich anfangen soll und welche ich joine. Ich bin der Meinung das die richtige für die linke Seite des Joins der "products_to_categories" Table wäre. Habe ein wenig damit herumgespielt aber "er" beschwert sich ständig da "products_id" in quasi jedem Table vorkommt. Er weiß also nicht was er ausgeben soll "column 'products_id' in field list is ambiguous". Versuche ich es mit einem Fakenamen z.b. "a.products_id" beschwert er sich weil es das Feld natürlich nicht gibt.

Eigentlich habe ich rein gar nichts :freak:

[EPIC_FAIL]

Wenn was mehrdeutig ist, musst du der Tabelle in der Query einen Alias geben.

Z.b.

select p.product_model, d.product_name from products p left join products_description d on p.id = d.id

[MiamiNice]

Ja, das weiß ich und habe die Anfrage auch entsprechend angepasst.
Auf Deine Anfrage schmeißt er raus "Unknown column 'p.product_model' in 'field list'".

Das selbe macht er auch bei meinen Anfragen.

[EPIC_FAIL]

Ja, das weiß ich und habe die Anfrage auch entsprechend angepasst.
Auf Deine Anfrage schmeißt er raus "Unknown column 'p.product_model' in 'field list'".

Das selbe macht er auch bei meinen Anfragen.

Da fehlt wohl noch ein "s" bei products. Sind das denn alle Spalten der Tabellen die du ja aufgelistet hast?
Die Query von mir war auch nur ein Beispiel, die gibt das nicht direkt aus was du haben möchtest.

[Jonny1983]

Mit Oracle SQL wäre das folgende Abfrage

SELECT products.products_model, products_description.products_name, categories_description.categories_name
FROM products
LEFT JOIN products_description ON
products.products_id = products_description.products_id
LEFT JOIN products_to_categories ON
products.products_id = products_to_categories.products_id
LEFT JOIN categories_description ON
products_to_categories.categories_id = categories_description.categories_id
WHERE products_description.language_id LIKE '1' and categories_description like '1'
GROUP BY products.products_model, products_description.products_name, categories_description.categories_name
ORDER BY products.products_model, products_description.products_name, categories_description.categories_name

Willst du das multiple Kategorien auch in mehreren Zeilen ausgegeben werden oder sollen alle Kategorien in einer Zeile durch Komma getrennt aufgelistet werden?

[MiamiNice]

Ne die Tabellen sind weitaus größer. Z.b. hat der Table "products" ca. 50 Spalten.

@Jonny1983: Vielen Dank ich versuche das mal umzuschreiben in MySQL kommt bei Deiner Abfrage "MySQL lieferte ein leeres Resultat zurück (d.h. null Datensätze). ( Die Abfrage dauerte 0.2120 Sekunden )" :)

Zu Deiner Frage, das ist eigentlich egal.

[MiamiNice]

Da fehlt wohl noch ein "s" bei products. Sind das denn alle Spalten der Tabellen die du ja aufgelistet hast?
Die Query von mir war auch nur ein Beispiel, die gibt das nicht direkt aus was du haben möchtest.

BTW:
Habe das "s" eingefügt an den relevanten Stellen.
Ergebnis: Unknown column 'p.id' in 'on clause'

Das das nicht die richtige Abfrage ist, ist mir klar. Aber genau da hänge ich aktuell auch fest, deswegen habe ich es getestet.

[Jonny1983]

Implizite Schreibweise des JOIN

SELECT products.products_model, products_description.products_name, categories_description.categories_name
FROM products, products_description, products_to_categories
WHERE products.products_id = products_description.products_id and
products.products_id = products_to_categories.products_id and
products_to_categories.categories_id = ategories_description.categories_id
and (products_description.language_id LIKE '1' and categories_description like '1')
GROUP BY products.products_model, products_description.products_name, categories_description.categories_name
ORDER BY products.products_model, products_description.products_name, categories_description.categories_name

Kann sein, dass Underlines durch Punkte ersetzt werden müssen oder genau andersrum.
Bei MS Access ist das der Fall.
Statt products_description.products_id schreibt man dann products_description_products_id

[Asaraki]

hier mal für "ganz normales SQL"


select p.products_model Artikelnummer, pd.products_name, cd.categories_name
from products P
-- selektiert alle artikel für die eine einglische bezeichnung vorhanden ist
join products_description pd
on p.products_id = pd.products_id
where pd.language_id = 1
-- nun fügen wir die kategorien hinzu
join products_to_categories pc
on p.products_id = pc.products_id
-- und nun die entsprechenden englischen texte zu den kategorien
join categories_description cd
on pc.categories_id = cd.categories_id
where cd.language_id = 1


die kommentare gehen bei dir ev. so nicht und müssten ausgebaut oder mit /* */ ersetzt werden

Kleiner Nachtrag... wenn du jetzt z.B. keine resultate bekommst, dann machste das erstmal step für step
d.h. du machst "select count(*)" und dann erstmal nur from products und dem ersten join z.b.
=> gibt das keine resultate, dann haste keine englischen texte und/oder der language_id code ist falsch
usw. usf...

Ich helf dir gern auch per PM, aber grundsätzlich wär's ja schade um den lerneffekt für die mitleser hier :-)

[MiamiNice]

@Jonny:

Vielen Dank für die Mühe. Scheinbar funktioniert die Schreibweise für MySQL leider nicht. Er hängt sich jedesmal auf mit folgender Fehlermeldung "Unknown column 'categories_description.categories_name' in 'field list'"

@Asaraki:

Dir auch erst mal vielen Dank.
Die ganze Abfrage funktioniert leider nicht

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'join products_to_categories pc on p.products_id = pc.products_id join categories' at line 1"

select p.products_model Artikelnummer, pd.products_name, cd.categories_name
from products P

Unknown column 'p.products_model' in 'field list'

Was so ziemlich genau der Fehler ist, den er bei meinen Versuchen auch immer bringt. Kann es sein das der SQL Server zu alt ist und er die Syntax nicht versteht? Ich kenne mich leider mit SQL zu wenig aus, quasi nur rudimentär. Kann das nicht beurteilen.

€: Generell sollte er auch Artikelnummern anzeigen für die es keine Übersetzung gibt und diese nicht auslassen sondern ein leeres Feld zeigen. Wobei mir auch gerade der Gedanke kommt das der Kunde nur mit dem Kategorienamen keine eindeutige Aussage hat. Wir verkaufen Oldtimer Ersatzteile für eine Menge Fahrzeuge und ob sich die Kategorie "Brake Disks" nun im Fahrzeug "Spider" oder "166" befindet, ist für den Kunden mit der Liste nicht ersichtlich. Eigentlich müsste er auch die Übergeordneten Kategorien ausgeben.

Es gibt einem Table "categories" in dem genau dies gespeichert ist. Sprich die "categories_id" und die "parent_id" (wird wohl die übergeordnete Kategorie sein). Nur leider sind die Kategorien auch verschachtelt sprich z.b. Spider -> Karosserieteile -> Blechteile -> Kotflügel -> 66-68.

Ich denke das sprengt einfach den Rahmen und ist wahrscheinlich gar nicht möglich.

[Jonny1983]

Funktioniert überhaupt irgendeine Abfrage?

Frag mal jede Tabelle ab

select *
from products

select *
from products_description

select *
from products_to_categories

Hab das Gefühl, dass du noch den Benutzer davor schreiben musst.

[MiamiNice]

Funktioniert überhaupt irgendeine Abfrage?

Frag mal jede Tabelle ab

select *
from products

select *
from products_description

select *
from products_to_categories

Hab das Gefühl, dass du noch den Benutzer davor schreiben musst.

Diese Dinge funktionieren natürlich, selbstredend und ungetestet.

[Asaraki]

Du brauchst vermultich folgendes :
set current path abc;

Wobei dir keinre hier den Path sagen kann, das müsstest du aus vorhandenen SQLs rausholen (path = owner der db)

Das mit den verschachtelten kategorien ist grundsätzlich kein problem, können wir gerne in einem zweiten schritt anschauen, aber bring erstmal die grundversion zum laufe.

Das mit den "auch produkte ohne übersetzung" ist kein problem, du machst einfach einen left join. left join bedeutet, dass alles aus der linken seite (erste tabelle) sowieso kommt und von recht (gejointe tabelle) alles dazu kommt, was matcht

Diese Dinge funktionieren natürlich, selbstredend und ungetestet.
sorry, aber ungetestet ist für mich gleichbedeutend mit "geht nicht"

ansonsten probier mal folgnedes aus :

select products.products_model
from products

wenn das auch nicht geht dann haben wir ein ganz anderes problem xD

[MiamiNice]

Sorry für das ungetestet.

select *
from products

select *
from products_description

select *
from products_to_categories

Selektiert jeweils die ganze Tabelle, der Output ist wie erwartet also der komplette Tabelleninhalt.

select products.products_model
from products

Als Ausgabe kommt eine Liste aller Artikel nur die Artikelnummer.
Funktioniert also auch wie erwartet.

Du brauchst vermultich folgendes :
set current path abc;

Das verstehe ich leider nicht.
Ich weiß schon "wo" die DB liegt und den Owner. Ich habe auch vollen Zugriff auf die DB. Nur leider habe ich kein tieferes Wissen über SQL, da ich kein Programmierer bin. Ich bin nur ein "Wald und Wiesen" Linux Admin :freak:

[Asaraki]

Dann kannst das mit dem Path vergessen, nicht nötig :-)

Also eventuell (schon lange kein MySQL mehr benutzt) musst du entweder als qualifier den ganzen Namen der table benutzen oder die Kurznamen mit "Tabelle as t" schreiben. Würde mal mit as probieren, also bei meinem SQL jeweils zwischen die Tabellennamen und die Kurznamen ein "as"

Bin unterwegs und werde das auf dem Handy nicht vortippen :-)

Edit : ist fast zu 100% nur das AS das fehlt. Probier das mal und wenn das geht zeig ich dir noch wie du die left joins einbaust und alle Kategorien auslesen kannst

Gäbe für den Fall ein paar schöne Sachen, muss aber erst schauen was MySQL davon überhaupt kann

[z3ck3]

SELECT
Product.products_model,
Productdescription.products_description,
Categorydescription.categories_name
FROM products Product
LEFT JOIN products_description Productdescription
ON Productdescription.products_id = Product.products_id
AND Productdescription.language_id = 1
LEFT JOIN products_to_categories ProductCategory
ON ProductCategory.products_id = Product.products_id
LEFT JOIN categories_description Categorydescription
ON Categorydescription.categories_id = ProductCategory.categories_id
AND Categorydescription.language_id = 1


Falls das nicht funktioniert exportiere mal das Schema der entsprechenden Tabellen. Sonst wird hier nur weiter im dunkeln gefischt. ;)


P.s.: hab es mit ner Datenbank getestet die ich auf Basis der Beschreibung im Eingangspost erstellt habe. Die Abfrage funktioniert. Die Frage ist halt nur ob die Beschreibung der Datenbank und ihrer Tabellenverknüpfungen stimmig ist ;)

[Asaraki]

Wie gesagt, MySql braucht das AS scheinbar für die Aliase auf Tabellen. Erstmal warten was das bringt :)

Btw deine Aliase sind imho grauenhaft ^^

[MiamiNice]

Vielen Dank für eure Unterstützung. Ich kann das leider heute nicht mehr testen da ich nicht mehr auf Arbeit bin und gerade keinen Zugabg habe. Ich werde morgen früh berichten.
Nochmals vielen vielen Dank.

[Asaraki]

Kein Stress :)

[z3ck3]

@Asaraki: Nix "AS". Wie bereits geschrieben funktioniert meine Abfrage. Habe sie mit einer Datenbank getestet die ich auf Basis seiner Beschreibung erstellt habe. Die Aliase habe ich Gewählt um die klarer erkennbar zu machen. Die Datenbank von XT Commerce scheint wesentlich grausamer zu sein als meine Aliase. Alte Meckerziege. ;)


btw. einer deiner Fehler war

...
from products P
join products_description pd
on p.products_id = pd.products_id
...

P ist nicht gleich p ;) Was mit ein Grund war noch mal die ganze Abfrage aufzubauen und die Aliase für einen Normalsterblichen leichter lesbar zu machen.

[RattuS]

Die Joins sind trivial. Interessant ist, wie du mit Produkten umgehen möchtest, die mehreren Kategorien zugeordnet sind, denn bei dieser Abfrage ist dann ggf. das Gruppieren der spannende Teil.

[Asaraki]

@Asaraki: Nix "AS". Wie bereits geschrieben funktioniert meine Abfrage. Habe sie mit einer Datenbank getestet die ich auf Basis seiner Beschreibung erstellt habe. Die Aliase habe ich Gewählt um die klarer erkennbar zu machen. Die Datenbank von XT Commerce scheint wesentlich grausamer zu sein als meine Aliase. Alte Meckerziege. ;)


btw. einer deiner Fehler war

...
from products P
join products_description pd
on p.products_id = pd.products_id
...

P ist nicht gleich p ;) Was mit ein Grund war noch mal die ganze Abfrage aufzubauen und die Aliase für einen Normalsterblichen leichter lesbar zu machen.

Hehe, war auch nicht böse gemeint, sorry kam vielleicht nicht rüber ^^ Aber wenn Alias, dann doch auch so, dass man was spart. Aber klar, jetzt versteh ich's, du wolltest es nur deutlich machen :)
Ja, hab das natürlich nicht getestet sondern gleich im Browser geschrieben, mea culpa.

Aber das AS dürfte trotzdem das Problem sein, du kannst halt schlecht von 'einer DB' auf eine andere DB schliessen. Hab kurz gegoogelt und scheinbar wollen zumindest gewisse MySQL Versionen das AS explizit haben. Warum auch immer ^^

Dass deine Abfrage bei dir funktioniert zweifle ich auch garnicht an :-)


@Rattus : Das ist schon klar und wurde auch bereits erwähnt, nur wahnsinnig hilfreich war dein Beitrag jetzt nicht ;-) Ich erklär ja einem Erstsemester auch nicht, dass seine relationale Algebra Aufgaben völlig lächerlich sind :D Ist hier immer noch das Hilfeforum.

Ich denke am einfachsten ist es, wenn Miami erstmal die grundsätzliche Abfrage hinkriegt, so dass wir alle Daten selektiert haben, die er auswerten will. Dann können wir nach Lust und Laune gruppieren und andere lustige Sachen einbauen ^^

[MiamiNice]

Guten Morgen,

die Abfrage von z3ck3 funktioniert weitestgehend. Als Ausgabe erfolgt "products_model" , "products_description" , "categories_name (in english)".

http://abload.de/img/unbenanntn1jjz.jpg

Die Spalte "products_description" ist leider komplett nicht befüllt. Die Artikel für die es eine englische Übersetzung gibt, da ist das Feld leer. Bei denen die keine Übersetzung habe, steht "NULL" drin (dies ist nicht getestet, eher eine Vermutung, teste ich später bzw. gleich!).

Die Tabelle "products_description" mal als Screny:

http://abload.de/img/products9fsdv.jpg

Ich denke das ist nur eine kleine Anpassung die darauf basiert das ich falsche Daten gegeben habe (habe jetzt nicht genau geschaut, bin erst seit 5 Mins auf Arbeit und muss erst was anderes erledigen).

Es wäre schön wenn Ihr das mit den übergeordneten Kategorien hinbekommen könntet, hoffe ich stresse euch nicht zu sehr.

Unsere Kategorien sind verschachtelt wie gestern schon geschrieben. Also z.b. Spider -> Karosserieteile -> Blechteile -> Kotflügel -> 66-68. Aber es gibt dort keine fixe länge der Verschachtelung. Es gibt z.b. auch Spider -> Literatur. Die dazugehörige Tabelle ist imho "categories" und deren Aufbau schaut so aus:

http://abload.de/img/unbenannt61l3f.jpg

Ich denke die Spalte "parent_id" zeigt die Übergeordnete Kategorie an.

Ich bedanke mich nochmals sehr bei euch für die Hilfe. Klasse Männers!

[RattuS]

Die Abfrage mit ordentlichem ANSI-92 JOIN für MySQL:


SELECT
`products`.`products_model`,
`products_description`.`products_name`,
`categories_description`.`categories_name`

FROM
`products`

INNER JOIN
`products_description`
ON
`products_description`.`products_id` = `products`.`products_id`
AND `products_description`.`language_id` = 1

INNER JOIN
`products_to_categories`
ON
`products_to_categories`.`products_id` = `products`.`products_id`

INNER JOIN
`categories_description`
ON
`categories_description`.`categories_id` = `products_to_categories`.`categories_id`
AND `categories_description`.`language_id` = 1

;


Da der Join hier über eine Mehrfachzuordnung läuft, wiederholen sich Produkte, die mehreren Kategorien zugeteilt sind. In den Dubletten unterscheidet sich dann lediglich die Kategorie. JOIN bzw. INNER JOIN berücksichtigt nur Datensätze, die vollständig verknüpft werden können, d.h. Produkte ohne englischen Name, Produkte ohne Kategorien und Kategorien ohne englischen Name werden ausgeschlossen. Falls du z.B. auch Produkte ohne Kategorie haben willst, mach aus dem INNER JOIN ein LEFT JOIN bei der zugehörigen Tabelle. Die entsprechende Spalte des Datensatzes ist dann NULL.

Es wäre schön wenn Ihr das mit den übergeordneten Kategorien hinbekommen könntet
Was meinst du mit der Kategorieverschachtelung? Willst du den gesamten Kategoriepfad in der Spalte oder wie?

[MiamiNice]

Guten Morgen Rattus,

Deine Abfrage funktioniert tadellos! Vielen Dank dafür.
Ich denke ich habe sogar etwas über MySQL gelernt, das ist ja nie verkehrt :biggrin:

Was meinst du mit der Kategorieverschachtelung? Willst du den gesamten Kategoriepfad in der Spalte oder wie?

Ja, der gesamten Kategoriepfad als Ausgabe in der letzten Spalte wäre echt toll. Wie geht man da vor? Ich weiß leider nicht mal im Ansatz wie ich das Auslesen sollte.

Für eine Idee in der Richtung wäre ich sehr dankbar.

[Asaraki]

Aloha,

Im Prinzip einfach, aber nicht ganz trivial das initial zu verstehen:
Du liest rekursiv die jeweilige parent Kategorie und hängst sie an die bereits gelesene kat an, während du die neu gelesene parent Kategorie als suchschlüssel erneut verwendest

[z3ck3]

Da ich sowas auch noch nie gemacht habe: Wie geht das? :D

Ich bin ja reichlich SQL "Behindert" weil von ORMs verwöhnt :D

[MiamiNice]

Das soll wohl mit der "with" Anweisung funktionieren.
Hier mal ein Beispiel: http://www.mayeruli.de/db2/rekursives-sql.php
Allerdings fehlen mir da wohl die programmiertechnischen Wurzeln.

[Asaraki]

Ich poste heute Abend oder morgen einen Entwurf, komme grad nicht dazu weil ich einen Fehler im DBMS entdeckt habe xD

[RattuS]

MySQL unterstützt keine hierarchischen Abfragen, obwohl sie Bestandteil des ANSI-99 sind. Du kannst das Problem aber, wie bereits erwähnt, über eine rekursive Abfrage lösen. Das ist allerdings nicht sonderlich effizient und bei größeren Tabellen (100.000+ Datensätze) nicht ratsam. Die Alternative wäre eine Adjazenzliste aufzubauen.

Hier wäre ein naive Lösung für dein Problem:

Funktion "resolveCategoryPath" erstellen

CREATE FUNCTION `resolveCategoryPath`(catID INT) RETURNS VARCHAR(1024)
BEGIN

DECLARE tempCatID INT;
DECLARE tempCatName VARCHAR(128);
DECLARE fullPath VARCHAR(1024);

SET tempCatID = catID;

SELECT
`categories`.`parent_id`

FROM
`categories`

INNER JOIN
`categories_description`
ON
`categories_description`.`categories_id` = `categories`.`categories_id`
AND `categories_description`.`language_id` = 1

WHERE
`categories`.`categories_id` = tempCatID

INTO tempCatID;

resolveLoop:
WHILE (tempCatID <> 0) DO

SELECT
`categories_description`.`categories_name`,
`categories`.`parent_id`

FROM
`categories`

INNER JOIN
`categories_description`
ON
`categories_description`.`categories_id` = `categories`.`categories_id`
AND `categories_description`.`language_id` = 1

WHERE
`categories`.`categories_id` = tempCatID

INTO tempCatName, tempCatID;

IF (FOUND_ROWS() = 0) THEN
LEAVE resolveLoop;
END IF;

SET fullPath = IFNULL( CONCAT(tempCatName, '/', fullPath), tempCatName );

END WHILE;

RETURN fullPath;

END;


Abfrage

SELECT
`products`.`products_model`,
`products_description`.`products_name`,
resolveCategoryPath(`products_to_categories`.`categories_id`) AS 'categories_name'

FROM
`products`

INNER JOIN
`products_description`
ON
`products_description`.`products_id` = `products`.`products_id`
AND `products_description`.`language_id` = 1

INNER JOIN
`products_to_categories`
ON
`products_to_categories`.`products_id` = `products`.`products_id`

;


Idealerweise baust du vor der Abfrage eine temporäre Tabelle, in der du jeden Pfad einer Kategorie nur einmal auflösen lässt. Diese temporäre Tabelle verbindest du dann via JOIN mit der Abfrage.


PS: Syntax Highlighting und weniger breite Tabs würden etwas mehr Übersicht bringen, aber wir sind ja hier nicht im Programmierung-Forum.

Oh Moment...

[z3ck3]

PS: Syntax Highlighting und weniger breite Tabs würden etwas mehr Übersicht bringen, aber wir sind ja hier nicht im Programmierung-Forum.

Oh Moment...

Das Forum hat sich schon ein paar mal gemeldet und will wohl aus den 90ern abgeholt werden :D

Mysql Function und Procedure hab ich mir bisher nie ansehen müssen. Ich glaub ich werd das aber mal aus reinem Interesse anschneiden.

[MiamiNice]

Ich poste heute Abend oder morgen einen Entwurf, komme grad nicht dazu weil ich einen Fehler im DBMS entdeckt habe xD

Hey Asaraki, magst Du Deinen Entwurf noch posten?