Webdevelopment
Drupal
Betriebssysteme
Design
Sonstige
Sa., 20/10/2012 - 23:02
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
Info hierzu, wenn man das ohne einen index auf hunderttausende rows macht...wird das nix :) also INDEX erzeugen.
Noch komplexer mit JOINS anderer Tabellen
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
- 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
Add new comment