MySQL Abfrage über 2 Schlüssel

Einklappen
X
 
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • MySQL Abfrage über 2 Schlüssel

    Hallo miteinander,

    gerade arbeite ich an einen Datenbankentwurf der wie folgt aussieht.
    Die Tabellen:

    In Tabelle objekt stehen die Personendaten
    In Tabelle hobbys stehen die Namen einiger Hobbys.
    In Tabelle obj_hob wird gespeichert welche Person welche Hobbys hat.

    Code:
    CREATE TABLE `hobbys` (
      `hob_id` mediumint(8) unsigned NOT NULL auto_increment,
      `hob_name` varchar(25) collate latin1_general_ci NOT NULL,
      PRIMARY KEY  (`hob_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=9 ;
    
    INSERT INTO `hobbys` (`hob_id`, `hob_name`) VALUES 
    (1, 'Fussball'),
    (2, 'Disco'),
    (3, 'Kartenspiel'),
    (4, 'Kochen'),
    (5, 'Einkaufen'),
    (6, 'Tischtennis'),
    (7, 'Chat'),
    (8, 'Schwimmen');
    
    CREATE TABLE `objekt` (
      `obj_id` mediumint(8) unsigned NOT NULL auto_increment,
      `obj_name` varchar(25) collate latin1_general_ci NOT NULL,
      PRIMARY KEY  (`obj_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7 ;
    
    INSERT INTO `objekt` (`obj_id`, `obj_name`) VALUES 
    (1, 'Anton'),
    (2, 'Anne'),
    (3, 'Berta'),
    (4, 'Fritz'),
    (5, 'Sabine'),
    (6, 'Otto');
    
    CREATE TABLE `obj_hob` (
      `obj_id` mediumint(8) unsigned NOT NULL,
      `hob_id` mediumint(8) unsigned NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
    
    INSERT INTO `obj_hob` (`obj_id`, `hob_id`) VALUES 
    (1, 1),
    (1, 3),
    (1, 5),
    (1, 7),
    (2, 1),
    (2, 2),
    (2, 3),
    (2, 4),
    (3, 2),
    (3, 4),
    (3, 6),
    (3, 8),
    (4, 5),
    (4, 6),
    (4, 7),
    (4, 8),
    (5, 1),
    (5, 3),
    (5, 5),
    (5, 7),
    (6, 1),
    (6, 2),
    (6, 3),
    (6, 4);
    Wenn ich nun wissen will welche Person welche Hobbys hat erstelle ich folgende Abfrage:

    Code:
    SELECT hob_name
    FROM obj_hob, hobbys
    WHERE obj_hob.hob_id = hobbys.hob_id
    AND obj_id = '1'
    So weit so gut

    Nun will ich eine Abfrage erstellen die alle Personen listet die eine Übereinstimmung der Abgefragten Hobbys hat.

    Erst habe ich folgende Abfrage erstellt

    Code:
    SELECT obj_name, hob_name
    FROM objekt, hobbys, obj_hob
    WHERE objekt.obj_id = obj_hob.obj_id
    AND obj_hob.hob_id = hobbys.hob_id
    AND (
    obj_hob.hob_id = '1'
    OR obj_hob.hob_id = '2'
    )
    Das Ergebnis zeigt mir alle Personen die entweder das Hobby mit der hob_id 1 oder 2 haben.
    Nun will ich eine Übereinstimmung mit beiden Hobbys haben. Ich habe diese Abfrage getestet, was aber ein leeres Resultat ergibt.

    Code:
    SELECT obj_name, hob_name
    FROM objekt, hobbys, obj_hob
    WHERE objekt.obj_id = obj_hob.obj_id
    AND obj_hob.hob_id = hobbys.hob_id
    AND (
    obj_hob.hob_id = '1'
    AND obj_hob.hob_id = '2'
    )
    Wie kann ich vorgehen, um die Personen zu ermitteln die bei den Hobbys eine exakte Übereinstimmung haben?

    lg

  • #2
    Es macht ja in dem Fall keinen Sinn den Namen des Hobbies auszugeben, da es einerseits mehr als einer ist und andererseits weisst du ja, nach welchen du suchst. So spontan müsste das ungefähr mit der folgenden Abfrage klappen:
    Code:
    SELECT 
    	obj_name,
    	COUNT(obj_id)
    FROM 
    	objekt o
    INNER JOIN 
    	obj_hob oh USING(obj_id)
    INNER JOIN 
    	hobbys h USING(hob_id) 
    WHERE 
    	oh.hob_id = 1
    OR 
    	oh.hob_id = 2
    HAVING
    	COUNT(obj_id) >= 2
    Falls du nur diejenigen willst, bei denen nur diese beiden Hobies vorkommen, dann nimm COUNT(obj_id) = 2
    Gruss
    H2O

    Kommentar


    • #3
      Hallo H2O,

      vielen Dank.

      HAVING COUNT() ist die Lösung die ich brauchte. Hat wunderbar geklappt.
      Wenn der COUNT gleich mit der Anzahl der Abgefragten Hobbys ist müssen die Übereinstimmungen gleich sein.

      Habe es jetzt für mich so verwendet da das ganze ein Teil einer größeren Abfrage ist.

      Code:
      SELECT obj_name
      FROM objekt, obj_hob
      WHERE objekt.obj_id = obj_hob.obj_id
      AND (
      obj_hob.hob_id = '1'
      OR obj_hob.hob_id = '2'
      )
      GROUP BY obj_hob.obj_id
      HAVING COUNT( obj_hob.obj_id ) =2
      lg
      Zuletzt geändert von flashbanane; 04.08.2008, 16:27.

      Kommentar


      • #4
        Hallo miteinander,

        ich habe meine Datenbank um die Tabelle kind erweitert.

        Code:
        CREATE TABLE `kind` (
          `k_id` mediumint(8) unsigned NOT NULL auto_increment,
          `obj_id` mediumint(8) unsigned NOT NULL,
          `k_name` varchar(25) collate latin1_general_ci NOT NULL,
          `alter` smallint(5) unsigned NOT NULL,
          PRIMARY KEY  (`k_id`)
        ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=11 ;
        
        -- 
        -- Daten für Tabelle `kind`
        -- 
        
        INSERT INTO `kind` (`k_id`, `obj_id`, `k_name`, `alter`) VALUES 
        (2, 1, 'Franzi', 10),
        (3, 1, 'Martin', 11),
        (4, 2, 'Martina', 12),
        (5, 3, 'Fred', 13),
        (6, 4, 'Marissa', 14),
        (7, 4, 'Thomas', 15),
        (8, 5, 'Hanna', 16),
        (9, 5, 'Reiner', 17),
        (10, 6, 'Jens', 18);
        Nun möchte ich zusätzlich in meiner letzten Abfrage das höchste alter der Kinder ausgeben und habe folgende Abfrage getestet.

        Code:
        SELECT objekt.obj_id, obj_name, max( k_alter ) 
        FROM objekt, obj_hob, kind
        WHERE objekt.obj_id = obj_hob.obj_id
        AND objekt.obj_id = kind.obj_id
        AND (
        obj_hob.hob_id = '1'
        OR obj_hob.hob_id = '2'
        )
        GROUP BY obj_hob.obj_id
        HAVING COUNT( obj_hob.obj_id ) >=2
        Nun werden auch Objekte mit ausgegeben, welche nur eines der beiden Abgefragten Hobbys haben und Zwei Kinder haben. Offensichtlich interpretiert die Datenbank die Kinderzahl dann als den COUNT in der HAVING Klausel.

        Weis vielleicht auch hier jemand wie man das verhindern kann und nur die Ergebnisse angezeigt werden die auch eine genaue Übereinstimmung der Hobbys haben.

        lg

        Kommentar


        • #5
          Da Kinder und Hobbies sich aus Zeitgründen gegenseitig ausschliessen, kann das wohl nicht funktionieren.

          Nein, im Ernst, mit diesen beiden Joins gibt es eben mehr Resultate, und der COUNT stimmt dann nicht mehr. Ich denke, du musst das mit einem Subselect lösen:
          Code:
          SELECT 
          	o.obj_id, 
          	o.obj_name,
          	(
          		SELECT 
          			MAX(k_alter) 
          		FROM
          			kind k
          		WHERE
          			k.obj_id = o.obj_id
          	) 
          FROM 
          	objekt o
          INNER JOIN
          	obj_hob oh ON o.obj_id = oh.obj_id
          WHERE
          	oh.hob_id = 1
          OR
          	oh.hob_id = 2
          GROUP BY 
          	oh.obj_id
          HAVING
          	COUNT(o.obj_id) >= 2
          Und mach bitte daraus nicht wieder so vorsintflutige Joins!
          Ausserdem würde ich nicht das Alter speichern (das ändert dauernd und je älter umso schneller), sondern das Geburtsdatum
          Gruss
          H2O

          Kommentar


          • #6
            Hallo H2O,
            danke dir, funktioniert wunderbar.

            Das mit den Kindern und Hobbys habe ich mir nur so einfallen lassen um mein Problem zu schildern.

            Das mit der Schreibweise werde ich mal bei einen neuen Projekt beherzigen aber wenn man mal auf eine Schreibweise eingefahren ist tut es das alte auch noch.

            lg

            Kommentar

            Lädt...
            X