How does one create a self-join in MySQL tables? I've only played a little with SQL selects so far, some inner joins, and now I have to create a many-to-many relationship between records in the same table. So, I have a links table with the local key and the foreign key, that will link record A to record B, but I'd like the same record to also link back from B to A.. This is what I have now, 'table' is the related table, in this case the same as the current table, local_id the key value of the current record:
"SELECT t.id, t.title FROM table AS t INNER JOIN links AS l ON t.id = l.foreign_id WHERE l.local_id = " & local_id; Do I just run this query twice, swapping the id_fields, and merge the result sets? Or do I have to use two records for each both-ways link? Or is there an ugly way that puts both ids in the same field and does a LIKE query? How is this usually solved? TIA! Regards ------------------------------------------------------------- Scott Haneda Tel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED] Novato, CA U.S.A. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
