--- Philippe Poelvoorde <[EMAIL PROTECTED]> wrote:
> 2006/4/8, Philippe Poelvoorde <[EMAIL PROTECTED]>: > > Hi, > > > > 2006/4/8, Dan Buettner <[EMAIL PROTECTED]>: > > > Seems like what you're looking for is a way to query your > database > > > more efficiently/quickly, and still find all links in either > > > direction. > > > > > > I think the use of a UNION statement should allow this. > Basically > > > write your query twice, joining on id1 the first time and id2 the > > > second time, with "UNION" in the middle. > > > > To select reciprocal friends of Dan (that is a friends relationship > in > > both ways), I would do that : > > select p.name from friends f1, friends f2, people p > > where p.peopleid=f1.id1 and f1.id2=1 > > AND f1.id2=f2.id1 > > AND f1.id1=f2.id2 > > UNION select p.name from friends f1, friends f2, people p > > where p.peopleid=f1.id2 and f1.id1=1 > > AND f1.id1=f2.id2 > > AND f1.id2=f2.id1; > > > > +------+ > > | name | > > +------+ > > | Matt | > > +------+ > > > > But I don't think it's the best solution in term of performance :) > > Sorry, can anyone comment this query ? How would you find the list of > symetric friend of Dan ? > Assuming that we have this table pseudo-structure CREATE TABLE person ( id name ) CREATE TABLE friends ( id from_person_id to_person_id ) And the following data: person _____________ _ID_|_NAME___ 1 | Alpha 2 | Bravo 3 | Charlie 4 | Delta 5 | Echo friends __________________ _ID _|_FROM_|_TO__ 1 | 1 | 1 1 | 1 | 2 1 | 1 | 3 1 | 1 | 4 1 | 1 | 5 1 | 2 | 1 1 | 3 | 1 1 | 4 | 1 This represents a graph of Alpha being friends with all of the other people but only Bravo, Charlie, and Delta return the favor (symmetric friendships). The relationship with Echo is asymmetrical. A query to pick out only those relationships that are symmetrical would look like: SELECT p1.name, p2.name FROM person p1 INNER JOIN friends f1 on f1.from_person_id = p1.id INNER JOIN friends f2 on f2.to_person_id = p1.id AND f2.from_person_id = f1.to_person_id INNER JOIN person p2 ON p2.id = f1.to_person_id; There are many ways to make that faster but this is the general form of the query. Shawn Green Database Administrator Unimin Corporation - Spruce Pine __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
