Abfrage über 2 Tabellen mit Schlüssel/Wertepaaren als Suchkriterien

Hallo zusammen,

war etwas schwierig, einen aussagekräftigen Titel zu finden...
Ich habe zwei Tabellen names und data, wobei in Tabelle data Schlüssel/Wertepaare abgelegt sind:

Tabelle names
id  | name
----+--------
 1  | name 1
 2  | name 2
 3  | name 3
 .  |   .
 .  |   .

Tabelle data
id | name_id | key | value
---+---------+-----+-------------
 1 |       1 | k0  | v0
 2 |       1 | k1  | v1
 3 |       2 | k0  | v0
 4 |       3 | k1  | v1
 . |       . |  .  |  .
 . |       . |  .  |  .

Nun möchte ich alle Namen aus Tabelle names abfragen, für die in Tabelle data bestimmte Kriterien hinterlegt sind, z.B. alle Namen, wo key = 'k0' und value = 'v0' .

SELECT 
   name 
FROM 
   names n, data d
WHERE
   n.id = d.name_id AND
   (d.key = 'k0' AND d.value = 'v1')

Die Abfrage liefert mir 'name 1' und 'name 2' zurück. Wenn ich jetzt aber alle Namen haben will, für die die Tupel ('k0','v0') und gleichzeitig ('k1', 'v1') existieren (im Beispiel trifft das nur auf 'name 1' zu) weiß ich nicht, wie ich die Abfrage formulieren soll.

SELECT 
   name 
FROM 
   names n, data d
WHERE
   n.id = d.name_id AND
   (d.key = 'k0' AND d.value = 'v1') AND -- Kriterium Tupel 1
   (d.key = 'k1' AND d.value = 'v1' )    -- Kriterium Tupel 2

ist ganz offensichtlich falsch und liefert keine Ergebnisse zurück, da nur eins der beiden Kriterien für eine Zeile erfüllt sein kann. Im optimalen Fall sollen beliebig viele Tupel überprüft werden können. Ich bin für jeden Hinweise dankbar.

Edit:
Die fehlerhafte Abfrage sollte natürlich so aussehen (Fix Zeile 7):

SELECT 
   name 
FROM 
   names n, data d
WHERE
   n.id = d.name_id AND
   (d.key = 'k0' AND d.value = 'v0') AND -- Kriterium Tupel 1
   (d.key = 'k1' AND d.value = 'v1' )    -- Kriterium Tupel 2

Cpp_Junky

OR

Nein, OR ist es leider nicht, da beide Kriterien erfüllt sein müssen.

SELECT 
   name 
FROM 
   names n, data d
WHERE
   n.id = d.name_id AND
   (d.key = 'k0' AND d.value = 'v0') OR -- Kriterium Tupel 1
   (d.key = 'k1' AND d.value = 'v1' )   -- Kriterium Tupel 2

liefert 'name 1', 'name 2' und 'name 3' zurück, weil für alle Zeilen mindestens eins der beiden Kriterien greift.

Hallo,

(vielleicht?) in etwa so:

SELECT 
   name 
FROM 
   names n, data d
WHERE
   n.id = d.name_id AND -- unten Klammern setzen, da AND i.A. stärker bindet als OR
   ((d.key = 'k0' AND d.value = 'v0') OR -- Kriterium Tupel 1
   (d.key = 'k1' AND d.value = 'v1' ))  -- Kriterium Tupel 2
GROUP BY (n.ID)
HAVING COUNT (n.ID) > 1 -- variiert je nach Anzahl der Tupel, die übereinstimmen sollen

MfG,

Probe-Nutzer

hustbaer

Jo so inetwa (Probe-Nutzer) kann man das machen.
Wenn die Paare mehrfach vorkommen können fehlt noch ein Subselect mit DISTINCT.

Oder einfacher:

SELECT
    name
FROM
    names AS n
WHERE
        EXISTS (SELECT * FROM data AS d WHERE d.name_id = id AND d.key = 'k0' AND d.value = 'v0')
    AND EXISTS (SELECT * FROM data AS d WHERE d.name_id = id AND d.key = 'k1' AND d.value = 'v1')
    AND EXISTS (SELECT * FROM data AS d WHERE d.name_id = id AND d.key = 'k2' AND d.value = 'v2')
    ...

Auf ner Datenbank mit gutem Query-Optimizer (z.B. MS-SQL) ist das gleich schnell oder sogar schneller als die GROUP BY Variante.

Auf ner Datenbank mit schlechtem Query-Optimizer könnte es ... unabgenehm langsam sein.

Vielen Dank,

ich werde beide Varianten mal ausprobieren und gucken, was schneller ist.
Das DBMS ist postgreSQL, das sollte einen recht guten Optimizer haben.

hustbaer schrieb:

...
Oder einfacher:

SELECT
    name
FROM
    names AS n
WHERE
        EXISTS (SELECT * FROM data AS d WHERE d.name_id = id AND d.key = 'k0' AND d.value = 'v0')
    AND EXISTS (SELECT * FROM data AS d WHERE d.name_id = id AND d.key = 'k1' AND d.value = 'v1')
    AND EXISTS (SELECT * FROM data AS d WHERE d.name_id = id AND d.key = 'k2' AND d.value = 'v2')
    ...

@hustbaer:
Was ist id in deinen Subselects? Ich habe das zu

SELECT
    name
FROM
    names AS n
WHERE
        EXISTS (SELECT * FROM data d, names n WHERE d.name_id = n.id AND d.key = 'k0' AND d.value = 'v0')
    AND EXISTS (SELECT * FROM data d, names n WHERE d.name_id = n.id AND d.key = 'k1' AND d.value = 'v1')

erweitert, aber das funktioniert so nicht (liefert mir alle Einträge aus names zurück, nicht nur die eigentlich gesuchten).

hustbaer

Ja, "id" ist "n.id".
Du hast aber mehr geändert, und deswegen bekommst du auch Unsinn raus.
Da du die Doof-Schreibweise eines INNER JOIN genommen hast hätte ich das auch fast übersehen.

Lösch das ", names n" in den Subselects weg. "n" muss natürlich auf das "n" im ässeren SELECT verweisen, sonst kann das ganze natürlich nicht funktionieren.

SELECT
    n.name
FROM
    names AS n
WHERE
        EXISTS (SELECT * FROM data d WHERE d.name_id = n.id AND d.key = 'k0' AND d.value = 'v0')
    AND EXISTS (SELECT * FROM data d WHERE d.name_id = n.id AND d.key = 'k1' AND d.value = 'v1')

Spitze, funktioniert!
Danke nochmals.