none
Datenbankabfrage Menge_All Minus Menge_mit_bestimmten_Tag

    Frage

  • Hallo,

    ich musste für eine Anwendung (in C# geschrieben) eine Erweiterung vornehmen, deren Daten in einer MDB-Datei liegen und komme erst später dazu die Anwendung anzupassen.

    Also habe ich eine Tabelle KATAGORIE erstellt, in der sich Tags befindet. Dann gibt es eine Tabelle GEGENSTEANDE und die Zuordnung zwischen den beiden Tabellen erfolgt über Tabelle ZUORDNUNG_GEGENSTAENDE_KATAGORIE (Z_G_K). Einem Gegenstand können beliebig viele oder auch kein Tag zugeordnet werden. Soweit Okay, eine Arbeitskollegin hat nun angefangen die Tags zuzuordnen. Naja, direkt in der Tabelle... Unschön... Mein Programm werde ich später anpassen.

    Nun soll ich eine Abfrage durchführen, in der alle Gegenstände ausgegeben werden, die nicht ein bestimmtes Tag haben. Und das bereit Probleme, da eine normale Abfrage über die drei Tabellen (normaler JOIN) dann nur die Datensätze ausgibt, die verknüpft sind.

    Also gedacht ich führe ein LEFT JOIN aus, der alle Positionen der Tabelle GEGENSTAENDE enthält und wenn in der Tabelle Z_G_K keine Zuordnung erfolgte, wird die Spalte dann NULL.

    Nun kann man so aber nur nach "not NULL" suchen.

    Was ich bei Oracle wohl gemacht hätte: Select über alle Produkte. Select über alle Produkte, die ein bestimmtes Tag haben und dann ein MINUS dieser beiden Menge, die mir alle Produkte bringen, die dieses Tag nicht haben. Das geht aber wohl nicht mit Access.

    Oder ich gebe jedem Gegenstand ein Default-TAG wie "Unnoetig_aber_nur_so_kann_ich_filtern". :-)

    Wie könnte ich das mit SQL in Access formulieren?

     

     

     


    Donnerstag, 2. Februar 2012 13:02

Antworten

  • Hallo!

    Wenn du die Felder als String zusammensetzt und dann vergleichst, verlierst du die Möglichkeit einen Index zu nutzen.

    => Table scan ... und der kann bei etwas mehr Datensätzen zu erhöhtem Kaffee-Konsum führen.

     

    Beispiel ink. Indexnutzung:

    SELECT h_id, u_id, p_id, bezeichnung
    FROM tbl_g G
    WHERE NOT EXISTS (Select * from tbl_z_g_p  Z WHERE Z.id_tag = 'i2011' AND Z.h_id = G.h_id AND Z.u_id = G.u_ID)

    Du kannst aber auch left join verwenden:

    SELECT h_id, u_id, p_id, bezeichnung
    FROM tbl_g G
    left join tbl_z_g_p Z
    ON (Z.id_tag = 'i2011' AND Z.h_id = G.h_id AND Z.u_id = G.u_ID)
    WHERE Z.h_id IS NULL

    Oder (falls das besser gefällt, da im Abfrageeditor darstellbar):

    SELECT h_id, u_id, p_id, bezeichnung
    FROM tbl_g G
    left join (select h_id, u_id from tbl_z_g_p Z where Z.id_tag = 'i2011') Z
    ON (Z.h_id = G.h_id AND Z.u_id = G.u_ID)
    WHERE Z.h_id IS NULL

    mfg
    Josef


    Code-Bibliothek für Access-Entwickler
    AccUnit - Testen von Access-Anwendungen

    Montag, 6. Februar 2012 17:03

Alle Antworten

  • HessischerBub wrote:
    > ...
    > Nun soll ich eine Abfrage durchführen, in der alle Gegenstände
    > ausgegeben werden, die nicht ein bestimmtes Tag haben. Und das
    > bereit Probleme, da eine normale Abfrage über die drei Tabellen
    > (normaler JOIN) dann nur die Datensätze ausgibt, die verknüpft
    > sind.
    >
    > Also gedacht ich führe ein LEFT JOIN aus, der alle Positionen der
    > Tabelle GEGENSTAENDE enthält und wenn in der Tabelle Z_G_K keine
    > Zuordnung erfolgte, wird die Spalte dann NULL.
    >
    > Nun kann man so aber nur nach "not NULL" suchen.
     
    Wie kommst du darauf?
    Zeig doch mal dein komplettes SQL Statement her.
     
    Wenn ich richtig verstehe, meinst du einfach:
     Datensätze aus A, die nicht in B sind
     
    Oder?
     
    --
    Servus
    Karl
    *********
    + SQL Server und .NET-Entwickler-Konferenz
    24./25.03.2012 Nürnberg, 28./29.04.2012 Hannover
     
     
    Donnerstag, 2. Februar 2012 13:13
  • Hallo Karl,

    so in der Art die in deiner FAQ habe ich das gelöst. Zumindest für jedes Jahr funzt das so.

    Was ich möchte:

    tbl_g

    ===

    id, bezeichnung

    1, etwas1

    2, etwas2

    3, etwas3

     

    tbl_tag

    =====

    id, tag

    def, defekt

    ver, verliehen

    i2011, inventur_2011

    i2012, inventur_2012

     

    tbl_z_g_t

    =======

    id_g, id_tag

    1, def

    1, i2011

    2, i2011

     

    Nun möchte ich eine Abfrage durchführen, in der ich alle Gegenstände ausgegeben bekomme, die kein Tag i2011 haben.  Und egal wieviel Tags einem Gegenstand zugeordnet wurden, soll die Abfrage den Gegenstand nur einmal aufführen.

    Es würde aber wohl schon reichen, wenn eine Abfrage alle Datensätze liefert und NULL in der Spalte "id_tag" durch "ohne_tag" tauscht, denn dann könnte ich einfach eine Abfrage darauf legen, die alle Datensätze <>"i2011" ausgibt.

    Ciao

    EDIT1: Das mit dem Ersetzen von NULL in "etwas" habe ich gefunden: NZ().

    EDIT2: Mathematisch werde ich aber wohl doch mit Mengen arbeiten müssen. Also Menge_Alle_Gegenstaende minus Menge_mit_Tag_i2011. Denn wenn ich das Tag "i2011" gesetzt habe, möchte ich den Gegenstand nicht ausgegeben bekommen, wenn da dann noch das Tag "def" zugewiesen wurde.


    Donnerstag, 2. Februar 2012 14:00
  • HessischerBub wrote:
    > ...
    > tbl_g
    > ===
    > id, bezeichnung
    > 1, etwas1
    > 2, etwas2
    > 3, etwas3
    >
    > tbl_tag
    > =====
    > id, tag
    > def, defekt
    > ver, verliehen
    > i2011, inventur_2011
    > i2012, inventur_2012
    >
    > tbl_z_g_t
    > =======
    > id_g, id_tag
    > 1, def
    > 1, i2011
    > 2, i2011
    >
    > Nun möchte ich eine Abfrage durchführen, in der ich alle Gegenstände
    > ausgegeben bekomme, die kein Tag i2011 haben.  Und egal wieviel Tags
    > einem Gegenstand zugeordnet wurden, soll die Abfrage den Gegenstand
    > nur einmal aufführen.
    > ...
     
    Es gibt sicher mehrere Varianten, z.B.
     
    SELECT tbl_g.id, tbl_g.bezeichnung
    FROM tbl_g LEFT JOIN tbl_z_g_t ON tbl_g.id = tbl_z_g_t.id_g
    WHERE tbl_g.id NOT IN (SELECT id_g FROM tbl_z_g_t WHERE id_tag="i2011")
     
    Wenn das nicht tut, was du willst, dann verrate noch die gewünschte
    Ergebnismenge deines Beispieles.
     
    --
    Servus
    Karl
    *********
    Access-FAQ: http://www.donkarl.com + SNEK
    SQL Server und .NET-Entwickler-Konferenz
    24./25.03.2012 Nürnberg, 28./29.04.2012 Hannover
     
     
     
    Donnerstag, 2. Februar 2012 16:11
  • Hallo Karl,

    wenn ich das mit WHERE .... NOT IN (....) löse, kann ich mir das mit dem LEFT JOIN IMHO sparen und so formulieren:

    SELECT h_id, u_id, p_id, bezeichnung
    FROM tbl_g
    WHERE h_id & ", " & u_id & ", " & p_id NOT IN (SELECT h_id & ", " & u_id & ", " & p_id FROM tbl_z_g_p WHERE id_tag = 'i2011');

    Ich habe beides ausprobiert (auch mit LEFT JOIN). Ergebnis ist identisch.

    Wenn ich ein zusammengesetzten Primärschlüssel habe (bei mir h_id, u_id und p_id), habe ich das oben mit der Zusammenfassung durch "&" bei "where ...... NOT IN ...) richtig gemacht oder würde man das anders formulieren?

    Problem: Die Abfrage benötigt viel Zeit, wenn einige Tausend Gegenstände vorhanden sind und dann viele davon das Tag "i2011" zugewiesen bekamen. Ich meine mich dunkel zu erinnern das ich vor einigen Jahren das bei einer Datenbankabfrage gegen unser ERP (DB: Oracle) zu spüren bekam und dort deshalb auf folgende Lösung kam:

    select artikel

    from tabelle_komplettes_lager

    MINUS

    select unique(artikel)

    from tabelle_lagerbewegungsjournal

    where bestimmte_artikelbewegung_gefunden;

     

    Das lieferte dann alle Artikel die unberührt im Lager rumliegen. Die Laufzeit war rasant. MINUS gibt es laut Google aber nicht unter Access. Das müsste ich anders formulieren.

     

    Ich bedanke mich auf jeden Fall für deine Hilfe.

    Donnerstag, 2. Februar 2012 18:10
  • Hallo!

    Wenn du die Felder als String zusammensetzt und dann vergleichst, verlierst du die Möglichkeit einen Index zu nutzen.

    => Table scan ... und der kann bei etwas mehr Datensätzen zu erhöhtem Kaffee-Konsum führen.

     

    Beispiel ink. Indexnutzung:

    SELECT h_id, u_id, p_id, bezeichnung
    FROM tbl_g G
    WHERE NOT EXISTS (Select * from tbl_z_g_p  Z WHERE Z.id_tag = 'i2011' AND Z.h_id = G.h_id AND Z.u_id = G.u_ID)

    Du kannst aber auch left join verwenden:

    SELECT h_id, u_id, p_id, bezeichnung
    FROM tbl_g G
    left join tbl_z_g_p Z
    ON (Z.id_tag = 'i2011' AND Z.h_id = G.h_id AND Z.u_id = G.u_ID)
    WHERE Z.h_id IS NULL

    Oder (falls das besser gefällt, da im Abfrageeditor darstellbar):

    SELECT h_id, u_id, p_id, bezeichnung
    FROM tbl_g G
    left join (select h_id, u_id from tbl_z_g_p Z where Z.id_tag = 'i2011') Z
    ON (Z.h_id = G.h_id AND Z.u_id = G.u_ID)
    WHERE Z.h_id IS NULL

    mfg
    Josef


    Code-Bibliothek für Access-Entwickler
    AccUnit - Testen von Access-Anwendungen

    Montag, 6. Februar 2012 17:03
  • Hallo Josef,

    ich bin begeistert. :-)

    Ich habe mir die erste Variante (die mit NOT EXISTS) angesehen und damit lieferte die Abfrage in kurzer Zeit das Ergebnis. Die beiden anderen Versionen werde ich mir zu Lehrzwecken in einer ruhigen Minute angucken. Weil Oracle früher kein JOIN kannte, als ich SQL gelernt habe, habe ich immer INNER JOIN mit WHERE ... = .... abgebildet. Ich sollte wohl mal mit der Zeit gehen und mir das mit JOIN ansehen.

    Also Danke für die Lösung und den Erkenntnisgewinn.

    mfg

    Dienstag, 7. Februar 2012 07:38