Netzflut

Webdesign, Webentwicklung, Drupal & Zeuchs

MYSQL join auf einzelne tabelle

Inhaltsverzeichniss

    http://www.java2s.com/Tutorial/MySQL/0100__Table-Join/LEFTJoinsonetablewithitself.htm
    http://forums.devarticles.com/mysql-development-50/mysql-joining-same-table-7219.html
    http://forums.mysql.com/read.php?121,272765,275307

    SQL BEISPIELDATEN

    CREATE TABLE IF NOT EXISTS `Wort_Link` (
      `wort_link_id` int(11) NOT NULL AUTO_INCREMENT,
      `wort_id` int(11) NOT NULL,
      `woerterbuch_id` int(11) NOT NULL,
      `root_word` tinyint(1) DEFAULT NULL,
      `wortart_id` int(11) DEFAULT NULL,
      `hauptgruppe_id` int(11) DEFAULT NULL,
      `genus_id` int(11) DEFAULT NULL,
      `synonymgrad_link_id` int(11) DEFAULT NULL,
      `konnotation_link_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`wort_link_id`,`wort_id`,`woerterbuch_id`),
      UNIQUE KEY `wort_links_id_UNIQUE` (`wort_link_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=344835 ;
     
    --
    -- Dumping data for table `Wort_Link`
    --
     
    INSERT INTO `Wort_Link` (`wort_link_id`, `wort_id`, `woerterbuch_id`, `root_word`, `wortart_id`,
                             `hauptgruppe_id`, `genus_id`, `synonymgrad_link_id`, `konnotation_link_id`) 
    VALUES
    (1, 175, 1, 1, NULL, 1, NULL, 1, NULL),
     
    (2, 176, 1, 0, NULL, 1, NULL, 1, NULL),
    (3, 177, 1, 0, NULL, 1, NULL, 1, NULL),
    (4, 178, 1, 0, NULL, 1, NULL, 1, NULL),
     
    (6, 171, 1, 1, NULL, 2, NULL, 2, NULL),
     
    (7, 172, 1, 0, NULL, 2, NULL, 2, NULL),
    (8, 173, 1, 0, NULL, 2, NULL, 2, NULL),
    (9, 174, 1, 0, NULL, 2, NULL, 2, NULL);

    Query

    Man joint hier die Tabelle selbst!

    SELECT t2.wort_link_id AS root_wort_link, t1.wort_link_id, t1.wort_id
    FROM Wort_Link AS t1
    INNER JOIN Wort_Link AS t2 ON t1.hauptgruppe_id = t2.hauptgruppe_id 
    AND t1.root_word = 0 
    AND t2.root_word = 1

    Info hierzu, wenn man das ohne einen index auf hunderttausende rows macht...wird das nix :) also INDEX erzeugen.

    Noch komplexer mit JOINS anderer Tabellen

    SELECT t2.wort_link_id AS root_wort_link, t1.wort_link_id, t1.wort_id,
    Synonymgrad_Ebene2.ebene as ebene_syn2, Synonymgrad_Ebene1.ebene as ebene_syn1
    FROM Wort_Link AS t1
    INNER JOIN Wort_Link AS t2 ON t1.hauptgruppe_id = t2.hauptgruppe_id 
    AND t1.root_word = 0 
    AND t2.root_word = 1
    JOIN Synonymgrad_Link ON t1.synonymgrad_link_id = Synonymgrad_Link.synonymgrad_link_id
    JOIN Synonymgrad_Ebene2 ON Synonymgrad_Link.synonymgrad_link_id = Synonymgrad_Ebene2.synonymgrad_ebene2_id
    JOIN Synonymgrad_Ebene1 ON Synonymgrad_Link.synonymgrad_link_id = Synonymgrad_Ebene1.synonymgrad_ebene1_id
    Design&Code: