Hi all, I have to create a database where the elements of a table are related to elements on the same table. What is the best (optimised) way to do that?
I came with the model: CREATE TABLE locations( location_id int(11) auto_increment, location_name varchar(50) NOT NULL default '', UNIQUE KEY idx1 (id) ) type MyISAM; CREATE TABLE relations( relation_id int(11) auto_increment, location_id1 int(11) NOT NULL, location_id2 int(12) NOT NULL, distance float NOT NULL default '0', UNIQUE KEY idx1 (location_id1, location_id2) UNIQUE KEY idx2 (relation_id) ) type MyISAM; And the query will be like: SELECT location_name, location_name, distance FROM locations AS l, relations AS r WHERE l.location_id=r.location_id1 AND l.location_id=r.location_id2; Thanks for your help Pedro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
