MYSQL join auf einzelne tabelle

Body
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
Webdevelopment
Sat, 20/10/2012 - 23:02