MySQL Assoziation Problem - Anzeige von Verbindungen

---

Da ich nun schon viel zu lange an diesem MySQL-Problem sitze, nutze ich nun alle Ressourcen die mir zur Verfügung stehen.

Ich habe zwei Tabellen:

“key_assoc” => key_x, key_y
“keyword” => id, keyword

In key_assoc sind die Keyword-Verbindungen drinnen, nach dem Motto:

2|3
3|5
7|10
usw.

und in Keywords siehts dann z.b. so aus:

2|Haus
3|Garten
5|Pilz
7|Stein
10|Webdesign

Ich möchte nun die Keyword-Verbindungen ausgeben, aber nicht anhand ihrer ID, sondern anhand ihrer Namen, also:

Haus|Garten
Garten|Pilz
Stein|Webdesign

Leider bekomme ich es nicht hin, benötige ich ein Join? Wenn ja, welche Art? Stehe leider auf dem Schlauch.

Ich füge auch einfach mal die Testdaten mit ein:

CREATE TABLE `keyword` (
`id` int(10) unsigned NOT NULL auto_increment,
`keyword` varchar(255) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `keyword` (`keyword`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=60 ;

--
-- Daten für Tabelle `keyword`
--

INSERT INTO `keyword` VALUES (1, 'webdesign');
INSERT INTO `keyword` VALUES (2, 'usability');
INSERT INTO `keyword` VALUES (3, 'web');
INSERT INTO `keyword` VALUES (4, 'internet');
INSERT INTO `keyword` VALUES (5, 'accessibility');
INSERT INTO `keyword` VALUES (6, 'adsense');
INSERT INTO `keyword` VALUES (7, 'affiliate');
INSERT INTO `keyword` VALUES (8, 'seo');
INSERT INTO `keyword` VALUES (9, 'suchmaschinenoptimierung');
INSERT INTO `keyword` VALUES (10, 'blackhat');
INSERT INTO `keyword` VALUES (11, 'whitehat');
INSERT INTO `keyword` VALUES (12, 'marketing');
INSERT INTO `keyword` VALUES (13, 'sem');
INSERT INTO `keyword` VALUES (14, 'podcast');
INSERT INTO `keyword` VALUES (15, 'programmieren');
INSERT INTO `keyword` VALUES (16, 'börse');
INSERT INTO `keyword` VALUES (17, 'aktien');
INSERT INTO `keyword` VALUES (18, 'aktie');
INSERT INTO `keyword` VALUES (19, 'geld');
INSERT INTO `keyword` VALUES (20, 'vermögen');
INSERT INTO `keyword` VALUES (21, 'karriere');
INSERT INTO `keyword` VALUES (22, 'job');
INSERT INTO `keyword` VALUES (23, 'rente');
INSERT INTO `keyword` VALUES (24, 'sport');
INSERT INTO `keyword` VALUES (25, 'fitness');
INSERT INTO `keyword` VALUES (26, 'ausdauer');
INSERT INTO `keyword` VALUES (27, 'muskeln');
INSERT INTO `keyword` VALUES (28, 'ernährung');
INSERT INTO `keyword` VALUES (29, 'diät');
INSERT INTO `keyword` VALUES (30, 'bodybuilding');
INSERT INTO `keyword` VALUES (31, 'sixpack');
INSERT INTO `keyword` VALUES (32, 'kraft');
INSERT INTO `keyword` VALUES (33, 'google');
INSERT INTO `keyword` VALUES (34, 'code');
INSERT INTO `keyword` VALUES (35, 'coden');
INSERT INTO `keyword` VALUES (36, 'php');
INSERT INTO `keyword` VALUES (37, 'c++');
INSERT INTO `keyword` VALUES (38, 'java');
INSERT INTO `keyword` VALUES (39, 'visual basic');
INSERT INTO `keyword` VALUES (40, 'oop');
INSERT INTO `keyword` VALUES (41, 'quellcode');
INSERT INTO `keyword` VALUES (42, 'sourcecode');
INSERT INTO `keyword` VALUES (43, 'kaffee');
INSERT INTO `keyword` VALUES (44, 'klingelton');
INSERT INTO `keyword` VALUES (45, 'klingeltöne');
INSERT INTO `keyword` VALUES (46, 'jamba');
INSERT INTO `keyword` VALUES (47, 'sparabo');
INSERT INTO `keyword` VALUES (48, 'musik');
INSERT INTO `keyword` VALUES (49, 'crazy frog');
INSERT INTO `keyword` VALUES (50, 'handy');
INSERT INTO `keyword` VALUES (51, 'mp3');
INSERT INTO `keyword` VALUES (52, 'kostenlos');
INSERT INTO `keyword` VALUES (53, 'spass');
INSERT INTO `keyword` VALUES (54, 'fun');
INSERT INTO `keyword` VALUES (55, '3d');
INSERT INTO `keyword` VALUES (56, 'maya');
INSERT INTO `keyword` VALUES (57, 'character');
INSERT INTO `keyword` VALUES (58, 'modellierung');
INSERT INTO `keyword` VALUES (59, 'modell');

CREATE TABLE `key_assoc` (
`key_x` int(10) unsigned NOT NULL,
`key_y` int(10) unsigned NOT NULL,
`gewichtung` float(10,2) unsigned NOT NULL,
UNIQUE KEY `key_x` (`key_x`,`key_y`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Daten für Tabelle `key_assoc`
--

INSERT INTO `key_assoc` VALUES (19, 33, 1.75);
INSERT INTO `key_assoc` VALUES (19, 6, 1.13);
INSERT INTO `key_assoc` VALUES (33, 6, 0.18);
INSERT INTO `key_assoc` VALUES (19, 8, 0.45);
INSERT INTO `key_assoc` VALUES (19, 22, 0.40);
INSERT INTO `key_assoc` VALUES (6, 8, 0.20);
INSERT INTO `key_assoc` VALUES (6, 22, 0.20);
INSERT INTO `key_assoc` VALUES (8, 22, 0.20);
INSERT INTO `key_assoc` VALUES (12, 52, 0.60);
INSERT INTO `key_assoc` VALUES (12, 4, 1.27);
INSERT INTO `key_assoc` VALUES (52, 4, 0.87);
INSERT INTO `key_assoc` VALUES (12, 7, 0.50);
INSERT INTO `key_assoc` VALUES (7, 12, 0.57);
INSERT INTO `key_assoc` VALUES (7, 34, 0.57);
INSERT INTO `key_assoc` VALUES (12, 34, 0.29);
INSERT INTO `key_assoc` VALUES (33, 36, 0.45);
INSERT INTO `key_assoc` VALUES (33, 8, 2.17);
INSERT INTO `key_assoc` VALUES (33, 3, 3.22);
INSERT INTO `key_assoc` VALUES (36, 8, 0.25);
INSERT INTO `key_assoc` VALUES (36, 3, 0.25);
INSERT INTO `key_assoc` VALUES (8, 3, 0.25);
INSERT INTO `key_assoc` VALUES (33, 4, 2.75);
INSERT INTO `key_assoc` VALUES (33, 11, 0.67);
INSERT INTO `key_assoc` VALUES (4, 11, 0.17);
INSERT INTO `key_assoc` VALUES (53, 3, 0.50);
INSERT INTO `key_assoc` VALUES (33, 53, 1.20);
INSERT INTO `key_assoc` VALUES (8, 53, 0.09);
INSERT INTO `key_assoc` VALUES (36, 6, 0.80);
INSERT INTO `key_assoc` VALUES (3, 38, 0.48);
INSERT INTO `key_assoc` VALUES (3, 4, 2.17);
INSERT INTO `key_assoc` VALUES (38, 4, 0.33);
INSERT INTO `key_assoc` VALUES (36, 41, 0.40);
INSERT INTO `key_assoc` VALUES (36, 2, 0.40);
INSERT INTO `key_assoc` VALUES (36, 5, 0.40);
INSERT INTO `key_assoc` VALUES (41, 2, 0.20);
INSERT INTO `key_assoc` VALUES (41, 5, 0.20);
INSERT INTO `key_assoc` VALUES (2, 5, 0.20);
INSERT INTO `key_assoc` VALUES (33, 2, 0.58);
INSERT INTO `key_assoc` VALUES (33, 1, 0.58);
INSERT INTO `key_assoc` VALUES (3, 2, 0.98);
INSERT INTO `key_assoc` VALUES (3, 1, 0.23);
INSERT INTO `key_assoc` VALUES (3, 53, 0.23);
INSERT INTO `key_assoc` VALUES (2, 1, 0.15);
INSERT INTO `key_assoc` VALUES (2, 53, 0.15);
INSERT INTO `key_assoc` VALUES (1, 53, 0.15);
INSERT INTO `key_assoc` VALUES (4, 3, 1.34);
INSERT INTO `key_assoc` VALUES (43, 15, 0.73);
INSERT INTO `key_assoc` VALUES (43, 35, 1.17);
INSERT INTO `key_assoc` VALUES (15, 35, 0.18);
INSERT INTO `key_assoc` VALUES (43, 19, 0.44);
INSERT INTO `key_assoc` VALUES (43, 34, 0.44);
INSERT INTO `key_assoc` VALUES (43, 33, 0.44);
INSERT INTO `key_assoc` VALUES (35, 19, 0.22);
INSERT INTO `key_assoc` VALUES (35, 34, 0.22);
INSERT INTO `key_assoc` VALUES (35, 33, 0.22);
INSERT INTO `key_assoc` VALUES (19, 34, 0.11);
INSERT INTO `key_assoc` VALUES (34, 33, 0.11);
INSERT INTO `key_assoc` VALUES (4, 33, 0.20);
INSERT INTO `key_assoc` VALUES (4, 1, 0.20);
INSERT INTO `key_assoc` VALUES (4, 19, 0.33);
INSERT INTO `key_assoc` VALUES (4, 2, 0.20);
INSERT INTO `key_assoc` VALUES (4, 8, 0.20);
INSERT INTO `key_assoc` VALUES (4, 36, 0.20);
INSERT INTO `key_assoc` VALUES (4, 38, 0.20);
INSERT INTO `key_assoc` VALUES (33, 19, 1.26);
INSERT INTO `key_assoc` VALUES (33, 38, 0.70);
INSERT INTO `key_assoc` VALUES (1, 3, 0.20);
INSERT INTO `key_assoc` VALUES (1, 19, 0.20);
INSERT INTO `key_assoc` VALUES (1, 2, 0.20);
INSERT INTO `key_assoc` VALUES (1, 8, 0.20);
INSERT INTO `key_assoc` VALUES (1, 36, 0.20);
INSERT INTO `key_assoc` VALUES (1, 38, 0.20);
INSERT INTO `key_assoc` VALUES (3, 19, 0.15);
INSERT INTO `key_assoc` VALUES (3, 8, 0.15);
INSERT INTO `key_assoc` VALUES (3, 36, 0.15);
INSERT INTO `key_assoc` VALUES (19, 2, 0.05);
INSERT INTO `key_assoc` VALUES (19, 36, 0.05);
INSERT INTO `key_assoc` VALUES (19, 38, 0.05);
INSERT INTO `key_assoc` VALUES (2, 8, 0.05);
INSERT INTO `key_assoc` VALUES (2, 36, 0.05);
INSERT INTO `key_assoc` VALUES (2, 38, 0.05);
INSERT INTO `key_assoc` VALUES (8, 36, 0.05);
INSERT INTO `key_assoc` VALUES (8, 38, 0.05);
INSERT INTO `key_assoc` VALUES (36, 38, 0.05);
INSERT INTO `key_assoc` VALUES (3, 52, 0.86);
INSERT INTO `key_assoc` VALUES (36, 33, 0.67);
INSERT INTO `key_assoc` VALUES (4, 21, 0.33);
INSERT INTO `key_assoc` VALUES (4, 22, 0.33);
INSERT INTO `key_assoc` VALUES (21, 22, 0.33);
INSERT INTO `key_assoc` VALUES (34, 3, 0.50);
INSERT INTO `key_assoc` VALUES (53, 4, 1.25);
INSERT INTO `key_assoc` VALUES (48, 4, 0.50);
INSERT INTO `key_assoc` VALUES (17, 16, 0.67);
INSERT INTO `key_assoc` VALUES (33, 12, 1.13);
INSERT INTO `key_assoc` VALUES (4, 12, 0.13);
INSERT INTO `key_assoc` VALUES (19, 12, 0.13);
INSERT INTO `key_assoc` VALUES (3, 12, 0.85);
INSERT INTO `key_assoc` VALUES (3, 50, 0.67);
INSERT INTO `key_assoc` VALUES (51, 48, 1.00);
INSERT INTO `key_assoc` VALUES (51, 50, 0.75);
INSERT INTO `key_assoc` VALUES (12, 2, 0.20);
INSERT INTO `key_assoc` VALUES (6, 33, 0.67);
INSERT INTO `key_assoc` VALUES (18, 17, 0.50);
INSERT INTO `key_assoc` VALUES (33, 17, 0.43);
INSERT INTO `key_assoc` VALUES (17, 19, 0.29);
INSERT INTO `key_assoc` VALUES (17, 3, 0.29);
INSERT INTO `key_assoc` VALUES (19, 3, 0.14);

---

Verwandte Artikel

1. MySql Quickreferenz
2. CSS Sprites - Background Repeat Problem
3. MySQL - mal was komplexeres
4. Slideshow über MySQL Performance
5. MySQL Connection Management in PHP

am April 23, 2007 um 13:46 Uhr | in Allgemein | 4 Kommentare

1 Trackbacks/Pingbacks

1. Pingback:   MySQL - mal was komplexeres by Webdesignblog on Mai 17, 2007

3 Kommentare

1. Jeriko, April 23, 2007:

   SELECT b.keyword, c.keyword
   FROM key_assoc a
   LEFT JOIN keyword b ON a.key_x = b.id
   LEFT JOIN keyword c ON a.key_y = c.id

2. Holger, April 23, 2007:

   Ich hab deine Testdaten jetzt einfach mal links liegen lassen und die Anfrage einfach mal so aufgeschrieben:

   SELECT links.keyword, rechts.keyword
   FROM keyword links, keyword rechts, key_assoc mitte
   WHERE mitte.key_x = links.id AND mitte.key_y = rechts.id

   Die tut das folgende:
   An die Tabelle key_assoc (die ich mitte genannt habe) wird zu jedem der beiden key-Attribute jeweils eine “Instanz” der keyword-tabelle (als links und rechts) per Equi-Join rangejoint. Damit wird erreicht, dass die Keywords für die Keys zusätzlich verfügbar sind. Anschließend werden diese herangejointen Keywords ausgegeben.

   Habs jetzt nicht getestet, sollte aber funktionieren.

3. Christian, April 23, 2007:

   Perfekt!! Danke euch beiden, ihr glaubt nicht wie lange ich rumprobiert (und natürlich auch in einigen Ebooks sowie einem Forum gefragt) habe.

Tut mir leid, die Kommentarfunktion für diesen Beitrag ist geschlossen.