✔ MySQL: SQL Abfrage mit oder ohne Sub Query?

[tklustig]

Hallo,
folgendes Query liefert folgendes Resultat:
select id, parent_id from product;

Die Interpretation diese Ausgabe sei folgende:
Diejenigen, die eine Parent ID haben, sind die Variantenprodukte und die in parent_id hinterlegte ID ist die des Masterprodukts.
Die Abfrage, die mir alle Masterprodukte ohne Variantenprodukte liefert, dürfte stimmig sein und lautet wie folgt(kann leider kein ER Diagramm beisteuern):

SELECT CONCAT("Masterprodukt") AS ConcatenatedString,product.id AS primaerschluessel,product_translation.name AS description, product.product_number AS prduktnummer 
FROM product
LEFT JOIN product_translation ON product.id=product_translation.product_id
WHERE product.parent_id IS NULL
AND product_translation.language_id=UNHEX('2fbb5fe2e29a4d70aa5854ce7ce3e20b');

Nun meine Frage:
Wie formuliere ich das Query um
a) alle Variantenprodukte ohne das dazugehörige Masterprodukt zu bekommen?
b) für jedes einzelne Masterprodukt das dazugehörige Variantenprodukt zu bekommen?
zu a) Ist das stimmig?

SELECT CONCAT("Masterprodukt") AS ConcatenatedString,product.id AS primaerschluessel,product_translation.name AS description, product.product_number AS produktnummer 
FROM product
LEFT JOIN product_translation ON product.id=product_translation.product_id
WHERE product.parent_id IS NOT NULL
AND product_translation.language_id=UNHEX('2fbb5fe2e29a4d70aa5854ce7ce3e20b');

zu b)
Da fehlt mir der Ansatz. Benötige ich dafür ein Sub Query?

 

[Zvoni]

Es wäre hilfreich, anstatt Grafiken, echten Text hier einzufügen.
Welche MySQL-version?

zu a) Sieht gut aus

zu b)
Hier mal ein fiddle: DB Fiddle - SQL Database Playground

create table products (
  ID INT,
  Bezeichnung VARCHAR(255),
  Parent_ID INT);

INSERT INTO products VALUES(1,'Prod 1',NULL);
INSERT INTO products VALUES(2,'Prod 2',NULL);
INSERT INTO products VALUES(3,'Prod 3 Version von Prod 1',1);
INSERT INTO products VALUES(4,'Prod 4 Version von Prod 1',1);
INSERT INTO products VALUES(5,'Prod 5 Version von Prod 1',1);
INSERT INTO products VALUES(6,'Prod 6 Version von Prod 2',2);
INSERT INTO products VALUES(7,'Prod 7 Version von Prod 2',2); 

SELECT M.ID, M.Bezeichnung AS MBez, V.ID, V.Bezeichnung AS VBez FROM products As M
INNER JOIN products AS V ON V.Parent_ID=M.ID

Ergibt

ID	MBez	ID	VBez
1	Prod 1	3	Prod 3 Version von Prod 1
1	Prod 1	4	Prod 4 Version von Prod 1
1	Prod 1	5	Prod 5 Version von Prod 1
2	Prod 2	6	Prod 6 Version von Prod 2
2	Prod 2	7	Prod 7 Version von Prod 2

 

[tklustig]

auf Basis des obigen Posts habe ich das Query so umformuliert, dass es mir für jedes Masterprodukt den PK, die Produktnummer und die Bezeichnung liefert samt aller zugehöriger Variantenprodukte. Allerdings weiß ich nicht, wie man bei den Variantenprodukten ebenfalls die Bezeichnung in das SELECT mit einbringt, da diese Bezeichnungen allesamt aus der LEFT JOIN Tabelle kommen.

SET @languageID='2fbb5fe2e29a4d70aa5854ce7ce3e20b';
SELECT product.id, product.product_number,product_translation.name AS bezeichnnug,variant.id, variant.product_number FROM product
INNER JOIN product AS variant ON variant.parent_id=product.id
LEFT JOIN product_translation ON product.id=product_translation.product_id
WHERE product_translation.language_id=UNHEX(@languageID)
GROUP BY product.id,variant.id

 

[Zvoni]

Du brauchst nen zweiten left join auf translation
falls du es nicht hinkriegst, wirds montag bei mir

wichtig: welche MySQL version?

 

[tklustig]

mysql -V gibt folgendes zurück:
Ver 15.1 Distrib 10.4.32-MariaDB, for Win64 (AMD64), source revision c4143f909528e3fab0677a28631d10389354c491

 

[Zvoni]

mysql -V gibt folgendes zurück:
Ver 15.1 Distrib 10.4.32-MariaDB, for Win64 (AMD64), source revision c4143f909528e3fab0677a28631d10389354c491

Alles klar.
Montag kann ich dir die Lösung geben (bin gerade auf ipad)
ist einfach

 

[Zvoni]

Vorweg: Benutz keine reservierten Wörter als Spaltennamen.
"name" ist ein Keyword, und eine tickende Zeitbombe

Wie gesagt: Einfach:

Setup

create table products (
  ID INT,
  Parent_ID INT);
 
Create table products_translations (
  ID INT,
  Bezeichnung VARCHAR(255),
  Product_id INT);

INSERT INTO products VALUES(1,NULL);
INSERT INTO products VALUES(2,NULL);
INSERT INTO products VALUES(3,1);
INSERT INTO products VALUES(4,1);
INSERT INTO products VALUES(5,1);
INSERT INTO products VALUES(6,2);
INSERT INTO products VALUES(7,2);

INSERT INTO products_translations VALUES(1,'Prod 1',1);
INSERT INTO products_translations VALUES(2,'Prod 2',2);
INSERT INTO products_translations VALUES(3,'Prod 3',3);
INSERT INTO products_translations VALUES(4,'Prod 4',4);
INSERT INTO products_translations VALUES(5,'Prod 5',5);
INSERT INTO products_translations VALUES(6,'Prod 6',6);
INSERT INTO products_translations VALUES(7,'Prod 7',7);

Mit CTE (Bin mir nicht sicher, ob MariaDB10.4.32 das unterstützt)
DB Fiddle - SQL Database Playground

WITH
    PT    AS (SELECT P.ID, T.Bezeichnung, P.Parent_ID
            FROM products AS P
            LEFT JOIN products_translations AS T
            ON P.ID=T.Product_id)

SELECT M.ID, M.Bezeichnung AS MBez, V.ID, V.Bezeichnung AS VBez
FROM PT As M
INNER JOIN PT AS V ON V.Parent_ID=M.ID

Ergibt:

ID	MBez	ID	VBez
1	Prod 1	3	Prod 3
1	Prod 1	4	Prod 4
1	Prod 1	5	Prod 5
2	Prod 2	6	Prod 6
2	Prod 2	7	Prod 7

Ohne CTE: DB Fiddle - SQL Database Playground

SELECT M.ID, M.Bezeichnung AS MBez, V.ID, V.Bezeichnung AS VBez
FROM
(SELECT P.ID, T.Bezeichnung, P.Parent_ID
            FROM products AS P
            LEFT JOIN products_translations AS T
            ON P.ID=T.Product_id) As M
INNER JOIN
(SELECT P.ID, T.Bezeichnung, P.Parent_ID
            FROM products AS P
            LEFT JOIN products_translations AS T
            ON P.ID=T.Product_id) AS V
ON V.Parent_ID=M.ID

Ergibt

ID	MBez	ID	VBez
1	Prod 1	3	Prod 3
1	Prod 1	4	Prod 4
1	Prod 1	5	Prod 5
2	Prod 2	6	Prod 6
2	Prod 2	7	Prod 7

Und hier mit zwei LEFT JOIN: DB Fiddle - SQL Database Playground

SELECT M.ID, TM.Bezeichnung AS MBez, V.ID, TV.Bezeichnung AS VBez
FROM products AS M
INNER JOIN products AS V ON M.ID=V.Parent_ID
LEFT JOIN products_translations AS TM ON M.ID=TM.Product_id
LEFT JOIN products_translations AS TV ON V.ID=TV.Product_id

Ergibt

ID	MBez	ID	VBez
1	Prod 1	3	Prod 3
1	Prod 1	4	Prod 4
1	Prod 1	5	Prod 5
2	Prod 2	6	Prod 6
2	Prod 2	7	Prod 7

 

[Zvoni]

P.S.: Hab gesehen, dass du noch eine Language_ID in products_translations hast.
In dem Fall dürfte die Variante mit CTE nicht funktionieren, da CTE's keine Variablen unterstützen bzw. würde mit CTE funktionieren, aber dann muss die language_id im CTE mitgeliefert werden, und dann im JOIN selbst gefiltert werden

Als genereller Hinweis: In deinem "Versuch" oben machst du einen LEFT JOIN auf translations, filterst aber die language_ID im WHERE-Teil des Queries.
Dir ist klar, dass in dem Moment aus dem LEFT JOIN ein INNER JOIN wird?

Dieser Filter gehört in den ON-Teil des LEFT JOIN (Auch wenn Yaslaw vehement widersprechen würde!)

SET @languageID='2fbb5fe2e29a4d70aa5854ce7ce3e20b';
SELECT M.ID, COALESCE(TM.Bezeichnung, 'Keine Übersetzung gefunden') AS MBez,
V.ID, COALESCE(TV.Bezeichnung, 'Keine Übersetzung gefunden') AS VBez
FROM products AS M
INNER JOIN products AS V ON M.ID=V.Parent_ID
LEFT JOIN products_translations AS TM ON M.ID=TM.Product_id AND TM.language_id=UNHEX(@languageID)
LEFT JOIN products_translations AS TV ON V.ID=TV.Product_id AND TV.language_id=UNHEX(@languageID)

Weil dann kannst du mit einem COALESCE eine "fehlende" Übersetzung mit einem "Default" belegen.
Probier mal dieses Query auf ein Datenbeispiel mit fehlenden "Übersetzungen" an.

Hier ein fiddle: DB Fiddle - SQL Database Playground

 

[Yaslaw]

Dieser Filter gehört in den ON-Teil des LEFT JOIN (Auch wenn Yaslaw vehement widersprechen würde!)

Dein Wunsch ist mir Befehl. Aus Prinzip wiederspreche ich dir vehement!

 

[tklustig]

Vorab vielen Dank an ZVONI. Derart komplexe Queries übersteigen meine SQL Kenntnisse.
Puuh, also folgendes vorab: Die Spalte heißt nun mal 'name'. Ich kann Sie nicht umbenennen. Die Datenbank ist nicht von mir konzipiert worden. Ich hätte beispielsweise auto inkrementierte PK's verwendet anstatt Binärwerte. Die CTE Variante funktioniert schon mal nicht. Es erfolgt eine Endlosschleife ohne Error Meldung. Muss also XAMPP abwürgen und dann neu starten. Die Variante ohne CTE klappt in dieser Form:

SET @languageID='2fbb5fe2e29a4d70aa5854ce7ce3e20b';
SELECT master.id, master.name AS masterBez, variant.id, variant.name AS variantBez
FROM
(SELECT product.id, prodTrans.name, product.parent_id
FROM product
   LEFT JOIN product_translation AS prodTrans
ON product.id=prodTrans.Product_id WHERE prodTrans.language_id=UNHEX(@languageID)
) As master
INNER JOIN
(SELECT product.id, prodTrans.name, product.parent_id
FROM product
   LEFT JOIN product_translation AS prodTrans
ON product.ID=prodTrans.product_id WHERE prodTrans.language_id=UNHEX(@languageID)
) AS variant
ON variant.parent_id=master.id;

Wie aber integriere ich in dieses komplexe Query die Gruppierung(GROUP BY) nach dem Masterprodukt und dem Variantenprodukt, analog zu meinem Post von Freitag - 17:42 -. Auch ohne Aggregatsfunktion ist die Gruppierung zwingend erforderlich