PHP_Dude: SQL-Abfrage

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?

  1. 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

    1. 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.

      1. 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 ;-)

        1. 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.

      2. 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

  2. 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.

    1. 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

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

        Ich verzweifel hier noch.

        1. 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.

          1. 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.

  3. 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

    --
    -------------------
    ss:) zu:) ls:& fo:) de:< va:{ ch:? sh:) n4:( rl:? br:$ js:| ie:) fl:(
    1. 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'

      1. 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

        1. 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.

          1. Hi,

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

            MfG
            Rouven

            --
            -------------------
            ss:) zu:) ls:& fo:) de:< va:{ ch:? sh:) n4:( rl:? br:$ js:| ie:) fl:(
            1. 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
                )

              1. 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

                --
                -------------------
                ss:) zu:) ls:& fo:) de:< va:{ ch:? sh:) n4:( rl:? br:$ js:| ie:) fl:(
              2. 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

                1. 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

        2. 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

          1. 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

  4. 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

    1. 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