SQL-Abfrage

[PHP_Dude]

hallo forum,
ich hab hier eine Datenbankabfrage, die ich einfach nicht hinbekomme, und ehrlich gesagt weis ich auch nicht, ob man das in einem query überhaupt lösen kann.

Vereinfacht gesagt, habe ich 2 Tabellen: inserate und sonder
Die Tabelle inserate beinhaltet die Daten von Gebrauchtwagen und die Tabelle sonder enthält die Zuordnung Inserat->Sonderausstattung

Tabelle: inserate
id | auto
---------
 1 | Audi
 2 | BMW
 3 | Ford
 4 | Opel

Tabelle: sonder
id | iid | sid
--------------
 1 |  1  |  1
 2 |  1  |  2
 3 |  1  |  3
 4 |  1  |  15
 5 |  2  |  3
 6 |  2  |  4
 7 |  3  |  2
 8 |  4  |  1
 9 |  4  |  2
10 |  4  |  3
11 |  4  |  15
12 |  4  |  16

Ich will jetzt nach Autos suchen das die Sonderausstattungen 1,2,3,15 hat.
Folgenden Query hab ich bis jetzt:
SELECT inserate.id FROM inserate WHERE inserate.id NOT IN ( SELECT iid FROM sonder WHERE sid NOT IN (1,2,3,15) )

Dieser Query gibt mir zwar den Audi, aber nicht den Opel aus, da der Opel auch noch die Ausstattung Nr. 16 hat.

Wie muss der Query aussehen, damit ich alle Autos bekommen die wenigstens die verlangten Ausstattungen haben, oder auch noch andere?

[tok tok]

Villeicht hilftdirdas hier weiter :-)

http://72.14.207.104/search?q=cache:wzxce0P7E3oJ:ii.umit.at/teaching/SMIR1/kap11.pdf+query+tabelle&hl=de&gl=de&ct=clnk&cd=2&lr=lang_de

[PHP_Dude]

Villeicht hilftdirdas hier weiter :-)

http://72.14.207.104/search?q=cache:wzxce0P7E3oJ:ii.umit.at/teaching/SMIR1/kap11.pdf+query+tabelle&hl=de&gl=de&ct=clnk&cd=2&lr=lang_de

Also da brauch ich noch ne Weile, bis ich das intus hab.

[tok tok]

Also da brauch ich noch ne Weile, bis ich das intus hab.

schau mal, oder hier mal beispiel :-)

http://koblenz-net.de/shop/article/query.html

auserdem schaumal in Google und gib das suchwort

"query tabelle" du wirst staunen ;-)

[PHP_Dude]

Also da brauch ich noch ne Weile, bis ich das intus hab.

schau mal, oder hier mal beispiel :-)

http://koblenz-net.de/shop/article/query.html

auserdem schaumal in Google und gib das suchwort

"query tabelle" du wirst staunen ;-)

Irgendwie weis ich nicht, wie mir das helfen soll?
Wie ich Daten aus einer Datenbank hole weis ich ja, nur dieser Extremfall ist mir unklar.

[at]

Hallo.

http://72.14.207.104/search?q=cache:wzxce0P7E3oJ:ii.umit.at/teaching/SMIR1/kap11.pdf+query+tabelle&hl=de&gl=de&ct=clnk&cd=2&lr=lang_de

Also da brauch ich noch ne Weile, bis ich das intus hab.

Kleiner Tipp: Die Hilfe ist vermutlich nicht in der Zeichenkette, sondern auf der Seite zu finden.
MfG, at

[Struppi]

Tabelle: inserate
id | auto

1 | Audi
2 | BMW
3 | Ford
4 | Opel

Tabelle: sonder
id | iid | sid

1 |  1  |  1
2 |  1  |  2
3 |  1  |  3
4 |  1  |  15
5 |  2  |  3
6 |  2  |  4
7 |  3  |  2
8 |  4  |  1
9 |  4  |  2
10 |  4  |  3
11 |  4  |  15
12 |  4  |  16

Ich will jetzt nach Autos suchen das die Sonderausstattungen 1,2,3,15 hat.
Folgenden Query hab ich bis jetzt:
SELECT inserate.id FROM inserate WHERE inserate.id NOT IN ( SELECT iid FROM sonder WHERE sid NOT IN (1,2,3,15) )

Wieso denn NOT?

Wenn ich das richtig versteh willst du einfach

SELECT inserate.id FROM inserate LEFT JOIN sonder ON inserate.id = sonder.iid
WHERE sonder.sid IN (1,2,3,15)

Struppi.

[PHP_Dude]

Wieso denn NOT?

Wenn ich das richtig versteh willst du einfach

SELECT inserate.id FROM inserate LEFT JOIN sonder ON inserate.id = sonder.iid
WHERE sonder.sid IN (1,2,3,15)

Struppi.

Danke Struppi, ich glaub das wars.

Wie schon gesagt, hab ich das ganze einfach ausgedrückt.
Der ganze Query ist schon so lang, das ich das Ganze einfach nicht mehr durchblickt habe.

Aber ich hab die Hoffnung nie aufgegeben, das es eine einfache Lösung gibt.

thx
PHP_Dude

[PHP_Dude]

Nur das er jetzt wieder alle Autos findet wo eine Ausstattung, aber nicht ALLE verlangten, findet.

Ich verzweifel hier noch.

[Struppi]

Nur das er jetzt wieder alle Autos findet wo eine Ausstattung, aber nicht ALLE verlangten, findet.

Ich verzweifel hier noch.

Ach das kam bei der Frage nicht 100% rüber. In dem Falle musst du auf IN() verzichten und jedes einzelne Attribut mit AND verknüpfen.

Struppi.

[PHP_Dude]

Nur das er jetzt wieder alle Autos findet wo eine Ausstattung, aber nicht ALLE verlangten, findet.

Ich verzweifel hier noch.

Ach das kam bei der Frage nicht 100% rüber. In dem Falle musst du auf IN() verzichten und jedes einzelne Attribut mit AND verknüpfen.

Struppi.

Sorry, aber so kann das nicht funktionieren.

WHERE sonder.sid = 1
AND sonder.sid = 2
AND sonder.sid = 3
AND sonder.sid = 15

Die sid kann nicht alles gleichzeitig pro Datensatz sein.

[Rouven]

Hi,

na ja, man könnte vielleicht über ein COUNT tricksen:
SELECT iid, COUNT(*) AS merkmalscheck
FROM sonder
WHERE sid IN (1, 2, 3, 15)
GROUP BY iid
HAVING merkmalscheck >= 4

Die 4 entspricht der Anzahl der erforderlichen Merkmale.
Zu lesen als: Gib mir all diejenigen Inserate, die bei Suche nach den Merkmalen 1, 2, 3, 15 unter der selben IID mindestens 4 Treffer aufweisen...

MfG
Rouven

[PHP_Dude]

Hi,

na ja, man könnte vielleicht über ein COUNT tricksen:
SELECT iid, COUNT(*) AS merkmalscheck
FROM sonder
WHERE sid IN (1, 2, 3, 15)
GROUP BY iid
HAVING merkmalscheck >= 4

Die 4 entspricht der Anzahl der erforderlichen Merkmale.
Zu lesen als: Gib mir all diejenigen Inserate, die bei Suche nach den Merkmalen 1, 2, 3, 15 unter der selben IID mindestens 4 Treffer aufweisen...

MfG
Rouven

Der Ansatz ist schonmal nicht schlecht, aber damit funktioniert der IN() nicht mehr, da plötzlich 2 werte zurückgegeben werden.

Das sieht dann so aus (gekürzt):

SELECT inserate.id,auto_marken.name AS marke,auto_modelle.modell,auto_typen.name AS typ,inserate.vb,inserate.km,inserate.preis,inserate.adresse
        FROM inserate
          LEFT JOIN auto_marken ON inserate.marke=auto_marken.id
          LEFT JOIN auto_modelle ON inserate.modell=auto_modelle.id
          LEFT JOIN auto_typen ON inserate.typ=auto_typen.id
        WHERE inserate.active=1
          AND inserate.id IN (
            SELECT iid, COUNT(*) AS merkmalscheck
            FROM sonder
            WHERE sid IN (1,2,3,15)
            GROUP BY iid
            HAVING merkmalscheck >= 4
          )
          AND inserate.marke = '2'
          AND inserate.modell = '20'

[Klaus Mock]

Hallo,

Der Ansatz ist schonmal nicht schlecht, aber damit funktioniert der IN() nicht mehr, da plötzlich 2 werte zurückgegeben werden.

Ich kenne die verwendete datenbank zu wenig aber es könnte auch (auszugsweise)

AND inserate.id IN (
             SELECT iid
             FROM sonder
             WHERE sid IN (1,2,3,15)
             GROUP BY iid
             HAVING COUNT(*) >= 4
           )

funktionieren. Wie gesagt, je nach datenbank.
Und wenn das nicht funktioniert, kannst Du ja vielleicht auch auf

AND inserate.id IN (
     SELECT iid
       FROM ( SELECT iid, COUNT(*) as merkmalscheck
                FROM sonder
               WHERE sid IN (1,2,3,15)
            GROUP BY iid
              HAVING merkmalscheck) >= 4
       )
     )

zurückgreifen.

Grüße
  Klaus

[PHP_Dude]

Hallo,

Der Ansatz ist schonmal nicht schlecht, aber damit funktioniert der IN() nicht mehr, da plötzlich 2 werte zurückgegeben werden.

Ich kenne die verwendete datenbank zu wenig aber es könnte auch (auszugsweise)

AND inserate.id IN (
             SELECT iid
             FROM sonder
             WHERE sid IN (1,2,3,15)
             GROUP BY iid
             HAVING COUNT(*) >= 4
           )

funktionieren.

Es funktioniert mit diesem Beispiel auch soweit, nur, das die Ergebnisse nicht richtig sind.
Wenn ich nämlich nach Autos suche die die Ausstattungen 14 und 15 haben, dann findet er auch den Audi, obwohl das ja nicht richtig ist.

Ich glaube, das der Ansatz mit dem zählen der Ergebniss, zu keinem richtigem Ergebnis führen kann.

PS.: Ich habe heute erst aus diesem Grund auf MySQL 5.0 ugegradet.

[Rouven]

Hi,

poste bitte nochmal deine Abfrage, ich kann mir das eigentlich nicht vorstellen...

MfG
Rouven

[PHP_Dude]

Hi,

poste bitte nochmal deine Abfrage, ich kann mir das eigentlich nicht vorstellen...

MfG
Rouven

Also, so sieht das Ganze momentan aus:

SELECT inserate.id,auto_marken.name AS marke,auto_modelle.modell,auto_typen.name AS typ,inserate.vb,inserate.km,inserate.preis,inserate.adresse
FROM inserate
  LEFT JOIN auto_marken ON inserate.marke=auto_marken.id
  LEFT JOIN auto_modelle ON inserate.modell=auto_modelle.id
  LEFT JOIN auto_typen ON inserate.typ=auto_typen.id
WHERE inserate.active=1
  AND inserate.id IN (
    SELECT iid
    FROM sonder
    WHERE sid IN (1,2,3,15)
    GROUP BY iid
    HAVING COUNT(*) >= 4
  )

[Rouven]

Hmh, also mir ist unklar wie da ein Auto rauskommen soll, dass nur eines der zwei gesuchten Merkmale hat (es sei denn, du hättest fälschlicherweise auf >=1 abgeprüft). Hast du mal alle JOINs rausgenommen und nur die Kernabfrage mit dem Subselect laufen lassen, ob die auch wirklich diese IID von dem Audi rausbringt? Ich kann mir das irgendwie wirklich nicht erklären...

MfG
Rouven

[Klaus Mock]

Hallo,

Wenn Du nach Autos suchen willst, die genau die gesuchten Ausstattungsmerkmale besitzen und keines mehr oder weniger, dann darfst Du nicht having count(*) >= x verwenden, sondern having count(*) = x, wobei x für immer für die anzahl der abgefragten Merkmale steht.

Grüße
  Klaus

[PHP_Dude]

Hallo,

Wenn Du nach Autos suchen willst, die genau die gesuchten Ausstattungsmerkmale besitzen und keines mehr oder weniger, dann darfst Du nicht having count(*) >= x verwenden, sondern having count(*) = x, wobei x für immer für die anzahl der abgefragten Merkmale steht.

Grüße
  Klaus

Nein, wenn ich nach einem Auto suche das Airbags, Autoradio und Schiebedach hat, dann möchte ich auch nur Autos zurückbekommen, die die genannten Ausstattungen oder sogar noch mehr Ausstattungen haben.

Das heisst ich will alle iid's die 1,2,3,15 oder auch noch anderen Ausstattungen Zugeordnet sind. Aber 1 und 2 und 3 und 15 müssen auf jeden Fall vorhanden sein.

gruss
PHP_Dude

[PHP_Dude]

Ich glaube, das ich letztes mal beim auspronieren einen Fehler gemacht habe und diese Lösung doch die richtigen Ergebnisse liefert.
Den Fehler den ich letztes mal hatte, kann ich nicht mehr reproduzieren.

Ich werde diesen Query jetzt so verwenden und möchte mich nochmal bei Allen ganz herzlich für die Hilfe bedanken.

lg
Gamerix

[Ilja]

yo,

Ich werde diesen Query jetzt so verwenden und möchte mich nochmal bei Allen ganz herzlich für die Hilfe bedanken.

nachdem meine abfrage im ersten posting anfängerhaft war und rouven von anfang an auf der richtigen spur, will ich trotzdem noch mal versuchen, etwas wertvolles zu mitzugeben.

ich hatte das problem mit der sonder tabelle ja schon mal angesprochen, dass ich einen zusammengesetzten schlüssel nehmen würde. das hat noch einen anderen hintergrund, nämlich dass es bei deinem daten-design rein theoretisch möglich wäre, einem auto die gleiche sonderausstattung mehrmals zuzuweisen. das ist sicherlich nicht so gewolllt, das design würde es aber zulassen.

um den entgegen zu wirken, kannst du einfach beim dem COUNT(*) ein DINSTICT mit reinnehmen, also COUNT(DISTINCT sid). das sollte es dann wasserfest machen. auch würde ich bem HAVING das größer zeichen wegnehmen und nur auf gleichheit prüfen. nur das macht dabei sinn.

wenn du aber den zusammengestzten schlüssel verwendest, den ich dir vorgeschlagen habe, dann kannst du dir das DISTINCT sparen.

Ilja

[Ilja]

yo,

Ich will jetzt nach Autos suchen das die Sonderausstattungen 1,2,3,15 hat.

Wie muss der Query aussehen, damit ich alle Autos bekommen die wenigstens die verlangten Ausstattungen haben, oder auch noch andere?

zum einen handelt es sich bei der tabelle sonder um eine m:n beziehungstabelle. und dabei sind die spaltennamen sehr ungünstig gewählt, auch kann man sich eine spalte sparen. mein vorschlag:

auto_id | sonder_id

beide spalten zusammen bilden dann den primäschlüssel.

allerdings gibt es noch eine besser lösung, dich ich dir empfehlen würde. in deinem falle würde ich die beziehungstabelle ganz auflösen und die jeweiligen sonderausstattungen als attribute mit in die erste tabelle auto nehmen. dann würde sich deine abfrage auch ganz von alleine lösen.

sicherlich kann man das kritisch sehen, aber in deinem falle geht das, weil die anzahl der unterschiedlichen sonderausstattungen endlich ist. außerdem spart man sich die joins über mehrere tabellen und macht es somit einfacher und schneller. einzig ein wenig mehr speicherplatz kostet es. das ist aber heutzutage nicht wiklrich ein argument.

falls dir das nicht zusagt, dann ist der ansatz von rouven der richtige, er hat ihn allerings nicht richtig umgesetzt. da du vergleiche über mehrere datensätze ausführen musst, gibt es meiner meinung nach nur zwei wege. einen join für jede sonderausstattung, sprich vier joins in deinem fall. dass ist aber sehr umständlich.

besser ist es über group by und count zu machen.

SELECT auto.id, COUNT(*)
FROM auto, sonder
WHERE auto.id = sonder.iid
AND sonder.sid = 1
AND sonder.sid = 2
AND sonder.sid = 3
AND sonder.sid = 15
GROUP BY auto.id
HAVING COUNT(*) = 4

Ilja

[Ilja]

yo,

habe da einen denkfehler ;-)

SELECT auto.id, COUNT(*)
FROM auto, sonder
WHERE auto.id = sonder.iid
AND sonder.sid IN (1, 2, 3, 15)
GROUP BY auto.id
HAVING COUNT(*) = 4

das sollte es eigentlich tun....

Ilja