Datenbankabfrage Menge_All Minus Menge_mit_bestimmten_Tag

Question

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?

 

 

 

Answer 1

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

 http://www.donkarl.com?FAQ3.16

 

Oder?

 

--

Servus

Karl

*********

Access-FAQ: http://www.donkarl.com

+ SQL Server und .NET-Entwickler-Konferenz

24./25.03.2012 Nürnberg, 28./29.04.2012 Hannover

 

 

Answer 2

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.

Answer 3

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

 

 

 

Answer 4

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.

Answer 5

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

Answer 6

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