Unterabfragen in Abfrage

[MarkusCS]

Hallo zusammen,
ich versuche nach langer Abwesenheit aus dem SQL-Geschäft wieder rein zu kommen. Dabei habe ich folgenden Knoten im Kopf den ich momentan einfach nicht gelöst bekomme.

Ich versuche aus einem Abfrageergebnis Unterergebnisse aus den selben Tabellen zu bekommen.

Zum Versuchsaufbau:

Ein Stoff hat die Attribute Material, Muster und Farbe.
Ein Schal hat einen Stoff aussen und einen Stoff innen.

Ich will nun in einer Abfrage zu jedem Schal die Stoffattribute ausgeben.

Die Tabellen sind wie folgt aufgebaut und testbefüllt:

tbl_Stoff:
+----+-------------+-----------+----------+
| ID | FK_Material | FK_Muster | FK_Farbe |
+----+-------------+-----------+----------+
|  1 |           1 |         1 |        1 |
|  2 |           1 |         4 |        2 |
+----+-------------+-----------+----------+

tbl_material:
+----+--------------+
| ID | Bez          |
+----+--------------+
|  1 | Wintersweaty |
+----+--------------+

tbl_muster:
+----+-----------------------+
| ID | bez                   |
+----+-----------------------+
|  1 | Uni                   |
|  2 | Streifen Schmal       |
|  3 | Streifen Breit        |
|  4 | Streifen Breit/Schmal |
+----+-----------------------+

tbl_farbe:
+----+-------------+
| ID | bez         |
+----+-------------+
|  1 | Grün        |
|  2 | Orange/Gelb |
|  3 | Petrol      |
|  4 | Jeans       |
|  5 | Gelb        |
|  6 | Rot         |
+----+-------------+

tbl_schal:
+----+-----------------+----------------+
| ID | FK_Stoff_Aussen | FK_Stoff_Innen |
+----+-----------------+----------------+
|  1 |               1 |              2 |
+----+-----------------+----------------+

Die Abfrage um einen Stoff in "Klarnamen aufzulösen" habe ich wie folgt gelöst:

select
    tbl_Stoff.ID as Stoff,
    tbl_Material.bez as Material,
    tbl_Muster.Bez as Muster,
    tbl_Farbe.bez as Farbe
from
     tbl_Material, tbl_Muster, tbl_Farbe, tbl_stoff
where
    tbl_stoff.FK_Material = tbl_Material.ID AND
    tbl_stoff.FK_Muster = tbl_Muster.ID AND
    tbl_stoff.FK_Farbe = tbl_Farbe.ID;
+-------+--------------+-----------------------+-------------+
| Stoff | Material     | Muster                | Farbe       |
+-------+--------------+-----------------------+-------------+
|     1 | Wintersweaty | Uni                   | Grün        |
|     2 | Wintersweaty | Streifen Breit/Schmal | Orange/Gelb |
+-------+--------------+-----------------------+-------------+

Ich bekomme es nur nicht hin das ich eine ähnliche Abfrage so hinbekomme, das mir der Stoff innen und Aussen für einen Schal aufgelöst wird.

Ich wünschte mir das Ergebnis wie folgt:

+----------+--------------+-----------------+---------------+--------------+-------------+----------------+-----------------------+-------------+
| Schal Nr | Stoff Aussen | Material Aussen | Muster Aussen | Farbe Aussen | Stoff Innen | Material Innen | Muster Innen          | Farbe Innen |
+----------+--------------+-----------------+---------------+--------------+-------------+----------------+-----------------------+-------------+
|        1 |            1 | Wintersweaty    | Uni           | Grün         |           2 | Wintersweaty   | Streifen Breit/Schmal | Orange/Gelb |
+----------+--------------+-----------------+---------------+--------------+-------------+----------------+-----------------------+-------------+

Ich habe nur eben einen Knoten im Hirn wie ich die Abfrage aufbauen soll. Da ich noch andere Kleidungsstücke in dieser Form "zerlegen" will ist diese verschachtelte Abfrage der Grundstock, da in anderen Kleidungsstücken bis zu 6 verschiedene Stoffe vorkommen.

Ich bin dankbar für jeden Hinweis der zur Lösung führt. Es geht mir nicht um eine fertige Lösung, sondern um den Weg zum Ziel. Ich habe auch schon im Forum gesucht, aber mir fällt kein griffiger Suchbegriff dafür ein. Wenn es also schon mal so ähnlich beantwortet worden ist würde ich mich auch über einen Link zu dieser Lösung freuen.

Ich danke schon mal im Vorraus für die Mühe das alles gelesen zu haben und bin gespannt was für Ideen ihr so habt..

 

[akretschmer]

Du wirst 2 mal joinen müssen. Stelle Deine Abfrage von jetzt implizieten Joins auf expliziete Joins um und vergib für Tabellen, die mehrfach gejoint werden, Alias-Namen.

 

[MarkusCS]

Hallo akretschmer,
danke für den Knotenlöser - ich habe nicht mehr an Alias-Namen gedacht. Ich habe es so gelöst:

SELECT 
l.id     AS Schal,                         /*ID des Schals            */
sa.id     AS StA,                         /*ID des Aussenstoffes  */
ma.bez     AS MatA,                         /*Aussenstoff  Material */
mua.bez AS MuA,                         /*Aussenstoff  Muster    */
fa.bez     AS FA,                            /*Aussenstoff  Farbe     */
si.id     AS StIn,                         /*ID des Innenstoffes    */
mi.bez     AS MatIn,                         /*Innenstoff  Material    */
mui.bez AS MuIn,                         /*Innenstoff  Muster     */
fi.bez     AS FIn                            /*Innenstoff  Farbe        */
FROM tbl_Schal l
   
    INNER JOIN tbl_Stoff sa             /*Auflösung des Aussenstoffes */
    ON l.fk_Stoff_Aussen = sa.id 
            INNER JOIN tbl_Material ma 
            ON sa.fk_material = ma.id
            INNER JOIN tbl_Muster mua
            ON sa.fk_muster = mua.id 
            INNER JOIN tbl_Farbe fa 
            ON sa.fk_farbe = fa.id
   
    INNER JOIN tbl_Stoff si                /*Auflösung des Innenstoffes */
    ON l.fk_Stoff_innen = si.id 
            INNER JOIN tbl_Material mi 
            ON si.fk_material = mi.id
            INNER JOIN tbl_Muster mui
            ON si.fk_muster = mui.id 
            INNER JOIN tbl_Farbe fi 
            ON si.fk_farbe = fi.id 
;

Und damit das gewünschte Ergebnis bekommen:

+-------+-----+--------------+------+------+------+--------------+-----------------------+-------------+
| Schal | StA | MatA         | MuA  | FA   | StIn | MatIn        | MuIn                  | FIn         |
+-------+-----+--------------+------+------+------+--------------+-----------------------+-------------+
|    1  |   1 | Wintersweaty | Uni  | Grün |    2 | Wintersweaty | Streifen Breit/Schmal | Orange/Gelb |
+-------+-----+--------------+------+------+------+--------------+-----------------------+-------------+

Bin mir jetzt zwar nicht Sicher ob das schon das die Optimale Lösung ist - aber sie funktioniert wie gewünscht. Falls noch jemand Optimierungsideen hat bin ich dankbar.

Auf alle Fälle Danke an akretschmer!

 

[akretschmer]

Bitte ;-)

Ein LEFT JOIN würde sicherlich auch gehen, da man ja von einer RI-Geschichte ausgehen kann. Diese INNER JOIN's sind irgendwie bei MySQL-Usern besonders beliebt ...