This question I am posting for a friend.... Below is post to another list...
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? One reply came in, and is below, with more info >If I'm not mistaken you need to alias the the same table twice. I'm also not >sure whether MySQL's query optimizer can deal with it: > > >SELECT t1.id, t1.title , t2.name >FROM table AS t1, table AS t2 >WHERE t2.link = t1.id This is a self-join one-to-many though, isn't it? >BTW, I think self joins are usually a sign of poor schema design - kind of a >filemaker thingy. The only time I found it semi-useful was in building a >hierarchical concept in a SQL table. Here, the records are people, and each person is linked to any number of other persons via relationships such as father, grandmother, aunt, husband etc. Record A can have a daughter-link to record B, which has a father-link back to record A. Both have many more links to other records, with some overlap (both can link to C, one as brother-relationship, one as son-relationship). One has to be able to add/remove any link(s). The whole picture doesn't need to be consistent, two records can link to eachother each using a grandfather-relationship, it will be up to the admin to avoid/fix this. I'm open to suggestions :-) ------------------------------------------------------------- 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
