zwei EXISTS für eine Tabelle

[gonzine42]

Moin in die Runde,

ich habe zwei Tabellen z.B. Autotypen und Ausstattung
Autotypen: UP, Caddy, A4, BMW3
In Ausstattung können für jeden Autotyp kein, ein oder mehrere Datensätze hinterlegt sein.

Ich suche jetzt alle Autotyp = VW, die in Ausstattung ein Radio haben, aber keine Klimaanlage.
Nach einigen Versuchen mit JOINs und verschachtelten SELECTs komme ich nicht weiter.

Hiermit müsste alle Autotypen erhalten, die eine Klimaanlage haben.

SELECT Autotypen.id
FROM Autotypen t
WHERE EXISTS
    (SELECT Ausstattung.id
     FROM Ausstattung a
     WHERE a.id =  t.id AND a = 'Klimaanlage')

Jetzt benötige ich noch einen NOT EXISTS. Wo muss ich den einfügen?
Kann ich den Ansatz überhaupt gebrauchen? Oder ist LEFT JOIN doch schneller?
Und wie muss ich weitermachen, damit noch das Radio und VW überprüft wird?

Besten Dank für Eure Unterstützung

gonzine

 

[drdimitri]

Einfach mit einer AND Bedingung hinzufügen.

AND NOT EXISTS(...)

Du könntest evtl. auch das Subselect erweitern, aber dazu müsste man die Tabellenstruktur kennen.
Ob ein Left Join schneller ist, wird man erst testen können, wenn die Tabellen mal einige hundertausend Zeilen enthalten.

 

[akretschmer]

Ich glaube, er will was anderes. Um es mal zu zeigen, allerdings mit PostgreSQL:

test=# select * from eigenschaften ;
 auto | eigenschaft
------+-------------
 vw  | klima
 vw  | radio
 vw  | lenkrad
 vw  | bremse
 opel | bremse
 opel | lenkrad
 opel | radio
(7 rows)

test=#
[\code]

welche autos haben klima un dradio?

[code]
test=# select auto from (select auto, array_agg(eigenschaft) as besitzt from eigenschaften group by auto) foo where besitzt @> array['radio','klima'];;
 auto
------
 vw
(1 row)
[\code]

welche autos haben NICHT klima und radio?

[code]
test=# select auto from (select auto, array_agg(eigenschaft) as besitzt from eigenschaften group by auto) foo where not besitzt @> array['radio','klima'];;
 auto
------
 opel
(1 row)
[\code]

Das geht in MySQL natürlich nicht, weil das solche Dinge und vieles andere auch nicht kann. Du kannst aber dennoch die Eigenschaften in einen String aggregieren und zählen, wie viele Eigenschaften jeweils drin sind.


Grüße aus dem heißen Malta.

 

[drdimitri]

Ich glaube, er will was anderes.

Ich glaub wir meinen schon das Gleiche, nur die Umsetzung ist anders.

 

[gonzine42]

Danke für Eure Antworten.
Die Struktur ist so, wie akretschmer sie dargestellt hat.

Da ich mich mit PostgreSQL nicht auskenne, muss ich mich in diesen Code erst reinknien, um ihn zu verstehen.

Ich habe jetzt meinen Code korrigiert und um den Teil NOT EXISTS ergänzt.

SELECT Autotypen.id, Ausstattung.merkmal 
FROM Autotypen t
WHERE EXISTS
    (SELECT Ausstattung.id
     FROM Ausstattung a
     WHERE a.id =  t.id AND a.merkmal = 'Klimaanlage')
AND NOT EXISTS
    (SELECT Ausstattung.id
     FROM Ausstattung a
     WHERE a.id =  t.id AND a.merkmal = 'Radio')

In der realen DB habe ich für die Tabelle Autotypen ca. 10000 DS und für die Tabelle Ausstattung ca. 170000 DS.
Irgendwo muss noch der Wurm drin sein, weil ich einen Timeout erhalte.

Gruß
gonzine

 

[gonzine42]

Mist. Gerade selber gemerkt, dass das zweite Alias falsch ist.

 

[gonzine42]

So, ich habe jetzt folgende Tabellen:

INSERT INTO `Autotypen` (`auto_id`, `marke`) VALUES
(1, 'VW'),
(2, 'Audi');

INSERT INTO `Ausstattung` (`ausstattung_id`, `auto_id`, `merkmal`) VALUES
(1, 1, 'Radio'),
(2, 1, 'Klima'),
(3, 2, 'Klima'),
(4, 2, 'Bremse');

Meine Abfrage liefert genau das gewünscht, die Marke "Audi".

SELECT t.auto_id, t.marke
FROM Autotypen AS t
WHERE EXISTS
    (SELECT a1.auto_id
     FROM Ausstattung AS a1
     WHERE a1.auto_id =  t.auto_id AND a1.merkmal = 'Klima')
AND NOT EXISTS
    (SELECT a2.auto_id
     FROM Ausstattung AS a2
     WHERE a2.auto_id =  t.auto_id AND a2.merkmal = 'Radio')

Wenn ich das ganze jetzt mit 10000 DS in "Autotypen" und 140000 DS in "Ausstattung" abfrage, dauert das sehr lange und ich erhalte einen Time Out.
D.h. heißt wohl, dass ich die Abfrage anders bauen muss?

Da benötige ich einen Hinweis, wie da ran gehen kann.

Danke

 

[drdimitri]

Welche Indices sind denn auf den Tabellen?

 

[akretschmer]

passende Indexe gesetzt?

Das mag funktionieren, aber skaliert nicht. Wenn Du nach weiteren Merkmalen suchst, die da oder nicht da sind, steigt der Aufwand grandios weiter an.

 

[drdimitri]

Der Algorithmus ist ebenso linear wie deine Version mit Aggregierung - nur eine andere Schreibweise. Lediglich die beiden IN Klauseln müssen bei Bedarf erweitert werden.

 

[gonzine42]

Ich habe die entsprechenden Indices gesetzt. Leider hat sich nichts gebessert.

 

[drdimitri]

Aber du verrätst uns nicht wie die aussehen?

 

[gonzine42]

Entschuldige.

ALTER TABLE `Autotypen`
  ADD PRIMARY KEY (`auto_id`),
  ADD KEY `marke` (`marke`);

ALTER TABLE `Ausstattung`
  ADD PRIMARY KEY (`ausstattung_id`),
  ADD KEY `auto_id` (`auto_id`),
  ADD KEY `merkmal` (`merkmal`);

 

[drdimitri]

Ich würd bei Ausstattung einen zusammengesetzten Index aus auto_id und merkmal verwenden und die anderen beiden löschen.

 

[gonzine42]

Ok, Danke. Das muss ich erst nachlesen, was das ist und wie das einrichte.