MYSQL join auf einzelne tabelle

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

SQL BEISPIELDATEN

  1. CREATE TABLE IF NOT EXISTS `Wort_Link` (
  2.   `wort_link_id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `wort_id` int(11) NOT NULL,
  4.   `woerterbuch_id` int(11) NOT NULL,
  5.   `root_word` tinyint(1) DEFAULT NULL,
  6.   `wortart_id` int(11) DEFAULT NULL,
  7.   `hauptgruppe_id` int(11) DEFAULT NULL,
  8.   `genus_id` int(11) DEFAULT NULL,
  9.   `synonymgrad_link_id` int(11) DEFAULT NULL,
  10.   `konnotation_link_id` int(11) DEFAULT NULL,
  11.   PRIMARY KEY (`wort_link_id`,`wort_id`,`woerterbuch_id`),
  12.   UNIQUE KEY `wort_links_id_UNIQUE` (`wort_link_id`)
  13. ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=344835 ;
  14.  
  15. --
  16. -- Dumping data for table `Wort_Link`
  17. --
  18.  
  19. INSERT INTO `Wort_Link` (`wort_link_id`, `wort_id`, `woerterbuch_id`, `root_word`, `wortart_id`,
  20.                          `hauptgruppe_id`, `genus_id`, `synonymgrad_link_id`, `konnotation_link_id`)
  21. VALUES
  22. (1, 175, 1, 1, NULL, 1, NULL, 1, NULL),
  23.  
  24. (2, 176, 1, 0, NULL, 1, NULL, 1, NULL),
  25. (3, 177, 1, 0, NULL, 1, NULL, 1, NULL),
  26. (4, 178, 1, 0, NULL, 1, NULL, 1, NULL),
  27.  
  28. (6, 171, 1, 1, NULL, 2, NULL, 2, NULL),
  29.  
  30. (7, 172, 1, 0, NULL, 2, NULL, 2, NULL),
  31. (8, 173, 1, 0, NULL, 2, NULL, 2, NULL),
  32. (9, 174, 1, 0, NULL, 2, NULL, 2, NULL);

Query

Man joint hier die Tabelle selbst!
  1. SELECT t2.wort_link_id AS root_wort_link, t1.wort_link_id, t1.wort_id
  2. FROM Wort_Link AS t1
  3. INNER JOIN Wort_Link AS t2 ON t1.hauptgruppe_id = t2.hauptgruppe_id
  4. AND t1.root_word = 0
  5. 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
  1. SELECT t2.wort_link_id AS root_wort_link, t1.wort_link_id, t1.wort_id,
  2. Synonymgrad_Ebene2.ebene as ebene_syn2, Synonymgrad_Ebene1.ebene as ebene_syn1
  3. FROM Wort_Link AS t1
  4. INNER JOIN Wort_Link AS t2 ON t1.hauptgruppe_id = t2.hauptgruppe_id
  5. AND t1.root_word = 0
  6. AND t2.root_word = 1
  7. JOIN Synonymgrad_Link ON t1.synonymgrad_link_id = Synonymgrad_Link.synonymgrad_link_id
  8. JOIN Synonymgrad_Ebene2 ON Synonymgrad_Link.synonymgrad_link_id = Synonymgrad_Ebene2.synonymgrad_ebene2_id
  9. JOIN Synonymgrad_Ebene1 ON Synonymgrad_Link.synonymgrad_link_id = Synonymgrad_Ebene1.synonymgrad_ebene1_id
Webdevelopment
Add new comment
The content of this field is kept private and will not be shown publicly.

Plain text

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <drupal-entity data-*>
  • Web page addresses and email addresses turn into links automatically.
  • Lines and paragraphs break automatically.