Retrieving a list of "friends"

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

fundu
Forum Newbie
Posts: 6
Joined: Tue May 01, 2007 11:03 am

Post by fundu »

Now I have created the tables

Table 1 : user

Code: Select all

id  |  Name    
-------------------
   1  | Vivek
   2  | Jatin
   3  | Anurag
   4  | Chandan
Table 2 : Friends_list

Code: Select all

id1 | id2    
-------------------
    1  |  2
    2  |  3
    3  |  1
    3  |  4
    4  |  1

Now I want to print 1's (Vivek's) friends i.e. Jatin, Anurag and Chandan
or 4's (Chandan's) friends list that is Vivek and Anurag

so what query Should I use??
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

That is the most strangely created friends list implementation that I've ever laid eyes on. But you want to do a query that looks for id1 and id2, and only selects rows where id1 or id2 equals the id you're looking for.

Then, select the id that is NOT the id of the person who's friends list is being loaded, and that's their friend.

Although, I'd think of a different way to do it, but I guess your way doesn't take up much space...
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Vivek's (1) friend is Jatin (2). Chandan's (4) friend is Vivek (1).

Try this:

Code: Select all

SELECT u.`Name`  
FROM `user` u 
    INNER JOIN `Friends_list` f 
    ON f.`id` = u.`id` 
WHERE u.`id` = 1 
OR u.`id` = 4;
Post Reply