sql Abfrage : nach gleichen tupeln suchen

[Becks21]

Hallo !

Ich bin ein ziemlicher SQL Neuling und beiße mir gerade die Zähne an einer (für mich recht schwierigen) SQL Abfrage aus. Meine Tabellen und Beziehungen sehen so aus:

TBauteile = {pkID; Name}
TPinDefinitionen = {pkID; PinNr; pinFunktionID}
TPinFunktionen = {pkID; fName}
TBauteilPinZuordnungen = {fkBauteilID; fkPinDefinitionID}

TPinDefinitionen:: pinFunktionID ist der Fremdschlüssel in die TPinfunktionen Tabelle.
TTBauteilPinZuordnungen::fkBauteilID ist der Fremschlüssel in die TBauteile Tabelle, und ::fkPinDefinitionID ist der Fremdschl. in die TPinDefinitionen Tabelle.

Über diese Tabellen und ihren Beziehungen kann also jedem Bauteil und all seinen Pins keine, eine oder mehrere (wohldefinierte) "Pin Funktionen" zugeordnet werden (es kann auch einem Pin eines Bauteils zwei oder mehrere Funktionen zugewiesen werden!).

Abfragen:

Gegeben sei eine _Liste_ von Tupeln bestehend aus PinNr und pinFunktionID (z.B. über eine GUI-Such-Maske), jetzt soll folgendes ermittelt werden:

A) Alle Bauteile deren entsprechenden Pins _mindestens_ die genannte(n) Funktionen zugeordnet wurden.

B) Alle Bauteile deren entsprechenden Pins _exakt_ die genannten(n) Funktionen und keine weiteren zugeordnet wurden.

Über ein INNER JOIN kann ich bereits eine Tabelle erstellen, die für jede Bauteile-ID und Pin-Nr die Pin-Funktionen auflistet. Wenn ich bspw. nur ein Filterkriterium hätte (meinetwegen nur Pin-Funktion), dann würde ich ds wohl über IN(...) machen, aber wie filtere ich nach meiner Tupel-Liste?

Hoffe jemand kann mir helfen, besten Dank im voraus!

Gruß, Micha

[fanderlf]

Das hab ich in meiner Oracle Datenbank mal versucht und ich glaub es funktioniert auch:

Die ersten 3 Tabellen sind die eigentlich Datentabellen. Wunschliste definiert die Tupel und pinnrfunktion definiert eine Tabelle in der BauteilId, PinNr und PinFunktionId zusammgefasst sind.

Die Tabelle PinFunktion habe ich weggelassen, weil sich in der Aufgabenstellung auf PinFunktionId berufen wurde, welche auch in TPinDefintionen vorkommt. Allerdings wäre es auch kein Problem sich darauf zu beziehen. Man müsste einfach pinnrfunktion dahingehend erweitern.

[highlight=sql]
with TBauteile as
(
SELECT 1 pkId, 'Widerstand' bname FROM dual
UNION ALL
SELECT 2 pkId, 'Kondensator' bname FROM dual
UNION ALL
SELECT 3 pkId, 'Spule' bname FROM dual
),
TPinDefintionen as
(
SELECT 1 pkId, 1 PinNr, 1 pinFunktionID FROM dual
UNION ALL
SELECT 2 pkId, 2 PinNr, 2 pinFunktionID FROM dual
UNION ALL
SELECT 3 pkId, 3 PinNr, 2 pinFunktionID FROM dual
),
TBauteilPinZuordnungen as
(
SELECT 1 fkBautailId, 1 fkPinDefinitionId FROM dual
UNION ALL
SELECT 1 fkBautailId, 3 fkPinDefinitionId FROM dual
UNION ALL
SELECT 2 fkBautailId, 1 fkPinDefinitionId FROM dual
UNION ALL
SELECT 2 fkBautailId, 3 fkPinDefinitionId FROM dual
UNION ALL
SELECT 2 fkBautailId, 2 fkPinDefinitionId FROM dual
),
wunschliste as
(
SELECT 1 pinnr, 1 pinfunktion FROM dual
UNION ALL
SELECT 3 pinnr, 2 pinfunktion FROM dual
UNION ALL
SELECT 2 pinnr, 2 pinfunktion FROM dual
),
pinnrfunktion as
(
SELECT bauteil.pkId bauteilid, pindefinition.PinNr, pindefinition.pinFunktionID
FROM TBauteile bauteil
INNER JOIN TBauteilPinZuordnungen bauteilpin ON bauteilpin.fkBautailId = bauteil.pkID
INNER JOIN TPinDefintionen pindefinition ON pindefinition.pkID = bauteilpin.fkPinDefinitionId
)
SELECT bauteil.bauteilid
FROM pinnrfunktion bauteil
LEFT JOIN wunschliste wunsch ON wunsch.pinnr = bauteil.pinnr AND wunsch.pinfunktion = bauteil.pinFunktionID
GROUP BY bauteil.bauteilid
HAVING COUNT(bauteil.bauteilid) =
(
SELECT COUNT(*) FROM wunschliste
)
[/highlight]

Die Lösung sollte Aufgabe b, entsprechen. Für Aufgabe a, den LEFT JOIN durch einen normalen JOIN ersetzen.

Wäre allerdings noch zu testen weil so etwas doch schwer zu überblicken ist

[Becks21]

Vielen Dank schonmal für deine Mühe!

Ich versuche gerade den Abfrage-Code nachzuvollziehen..
Gibt es evtl. eine Möglichkeit, ohne die "Zwischentabelle" "wunschliste" auszukommen?

Ich fürchte nämlich, dass ich im falschen Forum gepostet habe. Ich muss die Abfrage innerhalb von MS Access machen (Office XP Version), und da gibt es zwar das Schlüsselwort "WITH", nur mit ner anderen Bedeutung..

Vielleicht wäre es besser, den Thread zu verschieben.. sorry, ich wollte dir nicht deine Zeit klauen..

[fanderlf]

wenn Access subselects unterstützt:

[highlight=sql]
SELECT bauteil.bauteilid
FROM pinnrfunktion bauteil
LEFT JOIN
(
SELECT 1 pinnr, 1 pinfunktion FROM dual
UNION ALL
SELECT 3 pinnr, 2 pinfunktion FROM dual
UNION ALL
SELECT 2 pinnr, 2 pinfunktion FROM dual
) wunsch ON wunsch.pinnr = bauteil.pinnr AND wunsch.pinfunktion = bauteil.pinFunktionID
GROUP BY bauteil.bauteilid
HAVING COUNT(bauteil.bauteilid) = 3
[/highlight]

Wenn Du das Kommando so absetzt sollte in der HAVING Klausel einfach nur die Anzahl der Items in der Wunschliste stehen.

Prinzipiell funktioniert das Kommando so:
- Mache einen JOIN über beide Tabellen wobei von der Wunschliste alle Einträge hinzugenommen werden sollen (LEFT JOIN)
- Gruppe nach der BauteileId
- wähle nur diejenigen deren Anzahl von BauteilIds gleich der in der Wunschliste ist (BauteilIds durch den LEFT JOIN sind NULL - werden also nicht gezählt). Heisst wenn ein Bauteil nicht in der Wunschliste ist, ist die BauteilId NULL. Diese wird nicht gezählt, also hat man am Ende weniger als Bauteile in der Wunschliste -> darf nicht aufgelistet werden. Exakte Version.

Wenn man die Tabellen normal joint (INNER JOIN), dann findest Du erstmal alle BauteilIds die ein passendes Bauteil in der Liste haben. Dann muss ich nur noch die raussuchen, bei denen die Anzahl mit der Anzahl in der Wunschliste übereinstimmt. Mindestens Version.

Ganz ohne Subselect gings so:

[highlight=sql]
SELECT bauteil.bauteilid
FROM pinnrfunktion bauteil
WHERE
(bauteil.pinnr = 1 AND bauteil.pinFunktionID = 1)
OR
(bauteil.pinnr = 3 AND bauteil.pinFunktionID = 2)
GROUP BY bauteil.bauteilid
HAVING COUNT(bauteil.bauteilid) = 2
[/highlight]

Auch hier muss die Zahl in der HAVING Klausel mit der Anzahl der AND Bedingungen oben übereinstimmen. Allerdings lässt sich so nur die mindestens Bedingung darstellen. Aber exakt sollte bestimmt auch irgendwie gehen

[Becks21]

Hmm ok.. das sieht schon ne Ecke leichter aus..

Den SQL-String könnte ich mir dann ja per Visual Basic "zusammenbasteln",
im ersten Fall würde ich dann den sub-select dynamisch erzeugen, und im zweiten Fall das WHERE konstrukt..

Ein/Zwei Fragen bitte noch:

1. Bei dem Sub-select schreibst du immer "FROM dual", was heißt das bzw. was ist "dual"?

2. Mit den Sub-select Anweisungen a'la "SELECT 1 pinnr, 2 pinfunktion .. UNION ALL ..." erzeuge ich mir so ne Art temporäre Tabelle bestehend aus Tupeln im Speicher?

Besten Dank für deine Hilfe!

[Falk Prüfer]

Hallo,

da es dann doch eher spezifisch wird habe ich den Thread mal hierher verschoben und den bereits erstellten Doppelpost gelöscht.

"dual" ist übrigens eine "Tabelle" die keine ist . Dieses Schlüsselwort gibt es in verschiedenen DBMS, um einen Cursor mit beliebigen Spalten und einem einzigen Datensatz zu erhalten. Ein "SELECT 'Irgendwas' FROM dual" verpackt also das Literal 'Irgendwas' in einen Cursor.

Die von fanderlf verwendeten WITH-Konstrukte spielen übrigens für die Lösung keine Rolle. Damit erzeugt er einfach nur "temporäre" Tabellen zum Testen ohne diese physisch im DBMS anlegen zu müssen.
[highlight=sql]
WITH TBauteile AS
(
SELECT 1 pkId, 'Widerstand' bname FROM dual
UNION ALL
SELECT 2 pkId, 'Kondensator' bname FROM dual
UNION ALL
SELECT 3 pkId, 'Spule' bname FROM dual
)
[/highlight]
erzeugt z.B. eine "Tabelle" mit den Spalten pkId und bname und drei Datensätzen.
Sämtliche WITH-Konstrukte kannst du also für die Lösung ignorieren.

Gruß Falk

[fanderlf]

Sorry hätte ich vielleicht dazu schreiben sollen. Man weiss ja hier leider sehr sehr selten wie fit das gegenüber beim Programmieren ist und manche Dinge nimmt man einfach als selbstverständlich an.
Falk hats schon geschrieben. Die WITH Statements ersetzen einfach nur die physikalischen Tabellen bei Dir -> ignorieren.

[Becks21]

Besten Dank für die Erklärungen!

Interessant fande ich die Verwendung von "WITH" im Zusammenhang mit der "wunschliste"-Tabelle aus fanderfl erstem post, nur unterstützt MS Access scheinbar weder das WITH (in dieser Verwendung) noch kennt es die Cursor-Funktion "dual"

Die unten vorgeschlagene "Mindest"-Abfrage hab ich gerade in VBA umgesetzt, funktioniert super.

Die Erweiterung zur Exakt-Version sieht dann bei mir so aus, dass ich noch zusätzlich für alle Bauteile die genaue Anzahl an Pin-Zuordnungen ermittle, und diese Anzahl muss dann genau gleich der Anzahl an geforderten Pin-Zuordnungen sein (also im Beispiel von fanderfl dann =2).

In Sub-Selects aufgeteilt, sieht meine Exakt-Abfrage(n) dann so aus:

sqlAnzahlPinZuordnungenProBauteil:

SELECT TBauteile.pkID AS BauteilID, COUNT(TBauteile.pkID) AS AnzahlPinZuordnungen
FROM TBauteile INNER JOIN TBauteilPinZuordnungen ON TBauteile.pkID=TBauteilePinZuordnungen.fkBauteilID
GROUP BY TBauteile.pkID

sqlAllePinZuordnungenProBauteil:

SELECT CPs.BauteilID, TPs.PinNr, TPs.pinFunktionID, [sqlAnzahlPinZuordnungenProBauteil].AnzahlPinDefs
FROM sqlAnzahlPinZuordnungenProBauteil
INNER JOIN (TBauteile AS CPs
INNER JOIN TBauteilPinZuordnungen AS TPs ON CPs.pkID = TPs.BauteilID) ON 
[sqlAnzahlPinZuordnungenProBauteil].BauteilID = CPs.BauteilID

sqlSucheNachPinDefinitionen:

SELECT BauteilID
FROM sqlAllePinZuordnungenProBauteil
WHERE ((PinNr=1 AND pinFunktionID=2) OR (PinNr=2 AND pinFunktionID=1)) AND AnzahlPinDefs=2
GROUP BY BauteilID
HAVING COUNT(BauteilID)=2;

Meint ihr, die SQL-Abfragen sind so in Ordnung, oder doch kompletter humbuck?

[fanderlf]

Sie funktionieren und sind nicht überdimensional groß Ich würde sagen die sind OK. Bei einer großen Datenbank können solche Abfragen jedoch relativ lang dauern. Aber das kommt auf den Anwendungsfall an.

[Becks21]

Wie würde man denn vorgehen müssen, um diese Anfragen zu optimieren?

Die erste und zweite Sub-Query könnte man sicherlich noch "zusammenfassen", aber da hört es dann bei mir auch schon auf..