Frage zum Query mit verschachtelten Subselects

[Johnny B.]

Hallo geehrtes Forum,

das Normalisieren der DB hat es mit sich gebracht, daß ich nun zwei Ebenen runter muß mit meiner Abfrage. Also verschachtelte Subselects?!

In der Tabelle 'statistik' gibt es eine Spalte 'aktion_id'.
In der Tabelle 'aktionen' ist 'aktion_id' die primary id. Dieser ist jeweils eine 'typ_id' und 'typ' zugeordnet. 'typ' ist der Name der nächsten Tabelle, z.B. 'zeitungen'
In der Tabelle 'zeitungen' ist die 'typ_id' die primary id. Hier gibt es die Spalte 'beschreibung', an die ich heran möchte.

Die Abfrage, die mir dazu einfiel:

SELECT  aktion_id,                              -- hole aktion_id  
        (   SELECT beschreibung                 -- hole beschreibung  
            FROM (  SELECT typ                  -- aus der tabelle  
                    FROM aktionen               -- die in typ gespeichert ist  
                    WHERE id = aktion_id  
                  ) tmp                         -- alias gegen derived table error  
            WHERE id =                          -- und zwar von der id  
                 (  SELECT typ_id               -- die in aktionen  
                    FROM aktionen               -- als typ_id hinterlegt ist  
                    WHERE id = aktion_id  
                  )  
        )  
FROM statistik  
WHERE $datum_passt  

Erstaunlicherweise (at least für mich) erhalte ich folgende Fehlermeldung:
Unknown column 'aktion_id' in 'where clause'.

Ich google ein wenig und setze 'aktion_id' in Hochkomma, obschon mich das Gefühl beschleicht, hiermit der Lösung nicht näher gekommen zu sein. Die Fehlermeldung ändert sich daraufhin zu:
Unknown column 'beschreibung' in 'field list'

Dabei ist in jeder Tabelle, für die ein Wert in 'typ' gespeichert ist, die Spalte 'beschreibung' vorhanden. Gibt der Subselect, der 'typ' ausliest, falsche Werte zurück? Es gibt nur vier mögliche Werte, die 'typ' haben kann.

Was läuft hier schief? Kann mich jemand entwirren?

Besten Gruß
JOhnnY

[Vinzenz Mai]

Hallo,

Die Abfrage, die mir dazu einfiel:

SELECT  aktion_id,                              -- hole aktion_id

(   SELECT beschreibung                 -- hole beschreibung
            FROM (  SELECT typ                  -- aus der tabelle
                    FROM aktionen               -- die in typ gespeichert ist
                    WHERE id = aktion_id
                  ) tmp                         -- alias gegen derived table error
            WHERE id =                          -- und zwar von der id
                 (  SELECT typ_id               -- die in aktionen
                    FROM aktionen               -- als typ_id hinterlegt ist
                    WHERE id = aktion_id
                  )
        )
FROM statistik
WHERE $datum_passt

  

> Erstaunlicherweise (at least für mich) erhalte ich folgende Fehlermeldung:  
> Unknown column 'aktion\_id' in 'where clause'.  
> Was läuft hier schief? Kann mich jemand entwirren?  
  
Du gehst auf jeden Fall viel zu sparsam mit Aliasnamen um.  
  
  
Freundliche Grüße  
  
Vinzenz

[Johnny B.]

Hallo Vinzenz,

Du gehst auf jeden Fall viel zu sparsam mit Aliasnamen um.

SELECT  aktion_id akid,                              -- hole aktion_id  
        (   SELECT beschreibung tmp2beschreibung     -- hole beschreibung  
            FROM (  SELECT typ tabtyp                -- aus der tabelle  
                    FROM aktionen                    -- die in typ gespeichert ist  
                    WHERE id = 'akid'  
                  ) tmptab                           -- alias gegen derived table error  
            WHERE id =                               -- und zwar von der id  
                 (  SELECT typ_id tmpid              -- die in aktionen  
                    FROM aktionen                    -- als typ_id hinterlegt ist  
                    WHERE id = 'akid'  
                  )  
        ) tmp1beschreibung  
FROM statistik  
WHERE $datum_passt  

Na, dann schmeiß ich damit mal wild um mich. Ergebnis bleibt dasselbe:
Unknown column 'beschreibung' in 'field list'

Verwirrte Grüße
JOhnnY

[Vinzenz Mai]

Hallo,

Du gehst auf jeden Fall viel zu sparsam mit Aliasnamen um.

SELECT  aktion_id akid,                              -- hole aktion_id

(   SELECT beschreibung tmp2beschreibung     -- hole beschreibung
            FROM (  SELECT typ tabtyp                -- aus der tabelle
                    FROM aktionen                    -- die in typ gespeichert ist
                    WHERE id = 'akid'
                  ) tmptab                           -- alias gegen derived table error
            WHERE id =                               -- und zwar von der id
                 (  SELECT typ_id tmpid              -- die in aktionen
                    FROM aktionen                    -- als typ_id hinterlegt ist
                    WHERE id = 'akid'
                  )
        ) tmp1beschreibung
FROM statistik
WHERE $datum_passt

>   
> Na, dann schmeiß ich damit mal wild um mich. Ergebnis bleibt dasselbe:  
> Unknown column 'beschreibung' in 'field list'  
  
wie wäre es damit, dass Du dem DBMS über Aliasnamen klarmachst, auf welche \*Tabellen\* Du überhaupt zugreifen möchtest.  
  
Es wäre viel leichter nachzuvollziehen, was Du vorhast, wenn Du die beteiligten Tabellen, gefüllt mit ein paar Datensätzen, zeigtest und das gewünschte Ergebnis statt fehlerhaftes SQL.  
  
  
Freundliche Grüße  
  
Vinzenz

[Johnny B.]

Hallo Vinzenz,

Es wäre viel leichter nachzuvollziehen, was Du vorhast, wenn Du die beteiligten Tabellen, gefüllt mit ein paar Datensätzen, zeigtest und das gewünschte Ergebnis statt fehlerhaftes SQL.

ok, gerne:

statistik:
 id | datum      | typ | aktion_id
----+------------+-----+-----------
  1 | 2010-03-10 | G   | 2
  2 | 2010-03-10 | L   | 3
  3 | 2010-03-10 | C   | 2
  4 | 2010-03-11 | F   | 1
  5 | 2010-03-11 | C   | 4
  6 | 2010-03-11 | C   | 4

aktionen:
 id | start      | typ       | typ_id | direkt | weitere
----+------------+-----------+--------+--------+----------
  1 | 2010-03-08 | zeitungen | 5      | Y      | werte unwichtig
  2 | 2010-02-11 | zeitungen | 3      | Y      |
  3 | 2010-03-01 | anzeigen  | 4      | N      |
  4 | 2010-02-20 | mailing   | 2      | Y      |

zeitungen:
 id | verlag_id | beschreibung | auflage | weitere
----+-----------+--------------+---------+-----------
  3 |        15 | BILD         | 7865421 | werte unwichtig
  5 |         4 | Wochenschau  |   25000 |

anzeigen:
 id | beschreibung | aktuell_bis | weitere
----+--------------+-------------+----------------
  2 | www.markt.de | 2010-04-01  | werte unwichtig
  4 | www.work.net | 2010-05-12  |

mailing:
 id | beschreibung      | anzahl | weitere
----+-------------------+--------+----------------
  2 | Newsletterservice |   4500 | werte unwichtig
  4 | Mailservice SMS   |    250 |

Das Ergebnis soll dann so aussehen:

1 = Wochenschau
2 = BILD
3 = www.work.net
4 = Newsletterservice

Eigentlich scheint mir das eine Standard-Abfrage zu sein, die sich aus der Normalisierung ergibt: hole anhand der id die nächste id und dann einen Wert. Aber verschachtelte Subselects sind nicht wirklich gut zu handeln, befürchte ich. Ich habe bisher noch keinen JOIN angewendet, insofern weiß ich nicht, inwieweit es damit leichter gehen würde. Hier müßten halt gleich vier Tabellen auf einmal zusammengeführt werden. Daher dachte ich wäre ein Subselect vielleicht schlanker?

Besten Gruß
JOhnnY

[Vinzenz Mai]

Hallo,

Es wäre viel leichter nachzuvollziehen, was Du vorhast, wenn Du die beteiligten Tabellen, gefüllt mit ein paar Datensätzen, zeigtest und das gewünschte Ergebnis statt fehlerhaftes SQL.

statistik:
id | datum      | typ | aktion_id
----+------------+-----+-----------
  1 | 2010-03-10 | G   | 2
  2 | 2010-03-10 | L   | 3
  3 | 2010-03-10 | C   | 2
  4 | 2010-03-11 | F   | 1
  5 | 2010-03-11 | C   | 4
  6 | 2010-03-11 | C   | 4

aktionen:
id | start      | typ       | typ_id | direkt | weitere
----+------------+-----------+--------+--------+----------
  1 | 2010-03-08 | zeitungen | 5      | Y      | werte unwichtig
  2 | 2010-02-11 | zeitungen | 3      | Y      |
  3 | 2010-03-01 | anzeigen  | 4      | N      |
  4 | 2010-02-20 | mailing   | 2      | Y      |

zeitungen:
id | verlag_id | beschreibung | auflage | weitere
----+-----------+--------------+---------+-----------
  3 |        15 | BILD         | 7865421 | werte unwichtig
  5 |         4 | Wochenschau  |   25000 |

anzeigen:
id | beschreibung | aktuell_bis | weitere
----+--------------+-------------+----------------
  2 | www.markt.de | 2010-04-01  | werte unwichtig
  4 | www.work.net | 2010-05-12  |

mailing:
id | beschreibung      | anzahl | weitere
----+-------------------+--------+----------------
  2 | Newsletterservice |   4500 | werte unwichtig
  4 | Mailservice SMS   |    250 |

Das Ergebnis soll dann so aussehen:

1 = Wochenschau
2 = BILD
3 = www.work.net
4 = Newsletterservice

Verstehe ich Dich richtig:

Du möchtest zu allen (vier) Aktionen die Beschreibung aus der passenden Tabelle haben.

Wenn ja: Eine zweifache UNION und ein paar Joins:

1. Schritt: alles, was mit Zeitungen zu tun hat:

SELECT  
    aktionen.id,  
    zeitungen.beschreibung  
FROM  
    aktionen  
INNER JOIN  
    zeitungen  
ON  
    aktionen.typ_id = zeitungen.id  
WHERE  
    aktionen.typ = 'zeitungen'  

2. Schritt: alles, was mit Anzeigen zu tun hat:

SELECT  
    aktionen.id,  
    anzeigen.beschreibung  
FROM  
    aktionen  
INNER JOIN  
    anzeigen  
ON  
    aktionen.typ_id = anzeigen.id  
WHERE  
    aktionen.typ = 'anzeigen'

3. Schritt: alles, was mit mailing zu tun hat:

SELECT  
    aktionen.id,  
    mailing.beschreibung  
FROM  
    aktionen  
INNER JOIN  
    mailing  
ON  
    aktionen.typ_id = mailing.id  
WHERE  
    aktionen.typ = 'mailing'  

4. Schritt: Verknüpfe die einzelnen Abfragen mit UNION:

  
SELECT  
    aktionen.id aktion_id,                -- Aliasnamen für Spalten sind nur  
    zeitungen.beschreibung beschreibung   -- im ersten Select-Statement drin  
FROM  
    aktionen  
INNER JOIN  
    zeitungen  
ON  
    aktionen.typ_id = zeitungen.id  
WHERE  
    aktionen.typ = 'zeitungen'  
  
UNION  
  
SELECT  
    aktionen.id,  
    anzeigen.beschreibung  
FROM  
    aktionen  
INNER JOIN  
    anzeigen  
ON  
    aktionen.typ_id = anzeigen.id  
WHERE  
    aktionen.typ = 'anzeigen'  
  
UNION  
  
SELECT  
    aktionen.id,  
    mailing.beschreibung  
FROM  
    aktionen  
INNER JOIN  
    mailing  
ON  
    aktionen.typ_id = mailing.id  
WHERE  
    aktionen.typ = 'mailing'  

Fertig :-) Und ganz sicher viel schneller als komplexe Subselects.

Zu Joins gibts Lesestoff in SELFHTML aktuell (wobei ich meinen Artikel gerade ins Wiki übertrage):

Einführung in Joins
Fortgeschrittene Jointechniken

Freundliche Grüße

Vinzenz

[Johnny B.]

Hallo Vinzenz,

Verstehe ich Dich richtig:

Du möchtest zu allen (vier) Aktionen die Beschreibung aus der passenden Tabelle haben.

yepp...

Wenn ja: Eine zweifache UNION und ein paar Joins:

1. Schritt: alles, was mit Zeitungen zu tun hat:
2. Schritt: alles, was mit Anzeigen zu tun hat:
3. Schritt: alles, was mit mailing zu tun hat:
4. Schritt: Verknüpfe die einzelnen Abfragen mit UNION:

Fertig :-) Und ganz sicher viel schneller als komplexe Subselects.

aha. ok. Ich bin beeindruckt. Verstanden habe ich das zwar jetzt erstmal noch nicht, aber es sieht mal nicht so kompliziert aus. Ich werd mich gleich mal reinarbeiten.

Zu Joins gibts Lesestoff in SELFHTML aktuell (wobei ich meinen Artikel gerade ins Wiki übertrage):

Den ersten Artikel hatte ich bereits einmal gelesen und mich königlich über die Formulierung 'Motivation für die künstliche Herbeiführung des Problems' amüsiert. Es ist für mich quasi eine neue Art zu denken, dieses Relationale. Ist wie eine neue Sprache lernen. Der Anfang ist schwierig und vieles, was nachher einfach ist, wirkt unlösbar kompliziert. Ich glaube Du kannst Dir von einer Datenbank bereits Kaffee kochen und die Schuhe zubinden lassen, oder? WoOoW. Ist auf jeden Fall ein sehr weites Gebiet. Wer losgeht, steht auch schnell auf dünnem Eis. Daher mal ein DICKES DANKESCHÖN an Deine Hilfsbereitschaft <verneig>.

Besten Gruß
JOhnnY

P.S.
SELECT Kaffee
FROM Dose
UNION Wasser
WHERE temperatur = 100
JOIN Tasse
UNION Zucker, Milch

[Johnny B.]

Hallo Vinzenz,

5. Schritt: füge die Abfrage der Tabelle statistik ein
            schreibe dabei wirres Zeug, obschon es das richtige Ergebnis bringt

SELECT  u.akid aktion_id,  
        u.beschreibung,  
        u.anzahl  
FROM (  
    SELECT  aktion_id akid,  
            beschreibung,  
            (   SELECT COUNT( id )  
                FROM statistik  
                WHERE $datum_passt  
                AND typ = 'C'  
                AND aktion_id = akid  
            ) anzahl  
    FROM (  
        SELECT  
            aktionen.id aktion_id,                  -- Aliasnamen für Spalten sind nur  
            zeitungen.name beschreibung             -- im ersten Select-Statement drin  
        FROM  
            aktionen  
        INNER JOIN  
            zeitungen  
        ON  
            aktionen.typ_id = zeitungen.id  
        WHERE  
            aktionen.typ = 'zeitungen'  
  
        UNION  
  
        SELECT  
            aktionen.id,  
            anzeigen.domain  
        FROM  
            aktionen  
        INNER JOIN  
            anzeigen  
        ON  
            aktionen.typ_id = anzeigen.id  
        WHERE  
            aktionen.typ = 'anzeigen'  
  
        UNION  
  
        SELECT  
            aktionen.id,  
            mailings.beschreibung  
        FROM  
            aktionen  
        INNER JOIN  
            mailings  
        ON  
            aktionen.typ_id = mailings.id  
        WHERE  
            aktionen.typ = mailings  
    ) t  
) u  
WHERE u.anzahl > 0  
GROUP BY aktion_id                              -- und zähle alle Vorkommen für eine Aktion zusammen  
ORDER BY anzahl DESC, aktion_id ASC             -- sortiert nach Anzahl die meisten und Aktion_id nach unten 

Es verdichtet sich der Eindruck, obiger Query ist nicht optimal. Ich habe die einschränkende Bedingung, daß nur solche Datensätze angezeigt werden sollen, bei denen die Anzahl der Vorkommen Typ = 'C' > 0 ist. Um den Wert 'anzahl' benutzen zu können, brauche ich ja wieder eine Unterabfrage. Und die zweite brauche ich, weil ich nicht weiß, wie ich anders die Tabelle 'statistik' ins Spiel bringen kann. Mit in den UNION rein geht nicht, als SUBSELECT geht's nicht, weil er zwei Werte liefert. Also habe ich statt verschachtelter SUBSELECTs nun verschachtelte Unterabfragen. Trickreiche Kiste...

Wieder verwirrte Grüße
JOhnnY

[Ilja]

moin,

4. Schritt: Verknüpfe die einzelnen Abfragen mit UNION:

ich will hier noch mal auf "UNION ALL" hinweisen, nicht nur wegen der performance, sondern auch weil es zu "falschen" ergebnissen kommen kann mit UNION oder auch UNION ALL. es gilt eben immer den jeweils richtigen mengenoperator der beiden auszuwählen, mal ist UNION richtig, mal UNION ALL. und hier scheint mir UNION ALL der richtige zu sein.

Ilja

[ChrisB]

Hi,

In der Tabelle 'zeitungen' ist die 'typ_id' die primary id. Hier gibt es die Spalte 'beschreibung', an die ich heran möchte.

Du hast diese Tabelle aber nicht angegeben.

Unknown column 'beschreibung' in 'field list'

Dabei ist in jeder Tabelle, für die ein Wert in 'typ' gespeichert ist, die Spalte 'beschreibung' vorhanden. Gibt der Subselect, der 'typ' ausliest, falsche Werte zurück?

Schietegal, was der zurückgeben würde - es ist zu dem Zeitpunkt, wo die Query geparst und analysiert wird, nicht bekannt.
Und damit bleibt es dabei, dass du eine Spalte angegeben hast, die in keiner der benannten Tabellen vorhanden ist.

Diese Art von Dynamik, die du dir hier offenbar wünschst - den Tabellennamen, aus dem gelesen werden soll, erst zur Ausführung des Statements dynamisch zu ermitteln - unterstützt MySQL m.W. nicht.

MfG ChrisB

[dedlfix]

Hi!

Diese Art von Dynamik, die du dir hier offenbar wünschst - den Tabellennamen, aus dem gelesen werden soll, erst zur Ausführung des Statements dynamisch zu ermitteln - unterstützt MySQL m.W. nicht.

Mal unabhängig davon, ob das ein Lösungsweg für das Problem des OP wäre oder nicht: Man kann da was drehen. Man nehme dazu am besten eine Stored Procedure, und in dieser erstelle man sich in einem String das auszuführende Statement. Die variablen Teile kann man über CONCAT() mit den feststehenden Teilen zusammenfügen. Diesen String kann man dem PREPARE-Statement übergeben und ihn anschließend mit EXECUTE ausführen.

Allerdings sollte man es sich gut überlegen, ob man diese Von-hinten-durch-die-Brust-ins-Auge-Methode wählen will oder ob sich nicht doch was einfacheres findet, und sei es, dass man sich nochmal Gedanken um sein Tabellen-Layout macht.

Lo!

[Johnny B.]

Moin dedlfix,

Allerdings sollte man es sich gut überlegen, ob man diese Von-hinten-durch-die-Brust-ins-Auge-Methode wählen will oder ob sich nicht doch was einfacheres findet, und sei es, dass man sich nochmal Gedanken um sein Tabellen-Layout macht.

die Stored Procedures schau ich mir bei Gelegenheit auch nochmal an. Scheint eine Sache zu sein, die man häufiger sinnvoll anwenden kann.

Die Bemerkung zu den Gedanken zum Tabellen-Layout find ich lustig, denn die 'Motivation für die künstliche Herbeiführung des Problems' habe ich überhaupt nur bekommen, _weil_ ich mir Gedanken über die Normalisierung gemacht habe, die ich mir vorher noch nie gemacht habe.

Tatsächlich ist es so, daß ich anfangs in der Tabelle 'aktionen' noch eine Spalte 'beschreibung' geführt habe. Diese Spalte war leicht auszulesen, da sie sich auf einer Ebene mit der Spalte 'aktion_id' befand. Allerdings leuchtete aufgrund der Lektüre der Normalisierungsartikel eine rote Lampe bei mir auf! Der Inhalt dieser Spalte war doppelt vorhanden: in der Tabelle 'aktionen' sowie in den Detailtabellen z.B. 'zeitungen'. Doppelt = nicht gut! Das Feld 'beschreibung' gehört aber eher in die Tabelle 'Details einer Aktion' als zur Tabelle 'Übersicht aller Aktionen', schon weil es verschiedene Arten der Beschreibung gibt. Mal ist es ein Name, mal eine Domain, mal ein Dienstleister.

Die Idee, für die verschiedenen Detailaktionen seperate Untertabellen einzurichten, habe ich hier erhalten. Mein Bestreben ist, bei diesem Projekt die höchste Priorität auf ein _gutes_ Datenbankdesign zu legen und mich erst in zweiter Linie um die korrekten Abfragen zu kümmern.

Ok, da ich mir dieses Mal überhaupt zum ersten Mal ernsthaft Gedanken über ein Datenbankdesign mache, werde ich sicherlich noch einige Fehler und Umständlichkeiten einbauen. Falls Du eine Anregung für mich hättest, immer her damit...

Besten Gruß
JOhnnY

[dedlfix]

Hi!

Tatsächlich ist es so, daß ich anfangs in der Tabelle 'aktionen' noch eine Spalte 'beschreibung' geführt habe. Diese Spalte war leicht auszulesen, da sie sich auf einer Ebene mit der Spalte 'aktion_id' befand. Allerdings leuchtete aufgrund der Lektüre der Normalisierungsartikel eine rote Lampe bei mir auf! Der Inhalt dieser Spalte war doppelt vorhanden: in der Tabelle 'aktionen' sowie in den Detailtabellen z.B. 'zeitungen'. Doppelt = nicht gut!

Totnormalisieren bringts am Ende auch nicht. Worauf genau bezieht sich jeweils beschreibungen? Die Beschreibung einer Zeitung ("Inhalt: alles über $thema") gehört in deren Datensatz. Die Beschreibung einer Aktion ((Zeitung) "wegen Nachfrage ins Sortiment aufgenommen") gehören dorthin.

Das Feld 'beschreibung' gehört aber eher in die Tabelle 'Details einer Aktion' als zur Tabelle 'Übersicht aller Aktionen', [...]

Was ist der Unterschied zwischen Details und Übersicht? Üblicherweise ist das eine ein einzelner Datensatz und das andere die für die Übersicht benötigten aus einer Tabelle abgefragten und vielleicht zusammengefassten Daten.

[...] schon weil es verschiedene Arten der Beschreibung gibt. Mal ist es ein Name, mal eine Domain, mal ein Dienstleister.

Dafür reicht im Prinzip eine weitere Spalte. Eine eigene Tabelle braucht es da nur, wenn mehrere Beschreibungen unterschiedlichen Typs zu einer Aktion gehören.

Lo!