Join on a cyclic relation (beginner)

Question

I'm a beginner in queries and I'm struggling with one of them. Here are the two tables involved :

UserAndAddFriends

The askstobefriends table permit a user to add a friend in the application I m developping. The relational form of it is :

AskToBeFriends(ID (long), #UserAsker (long), #UserAsked (long), Accept (tinyInt))

So with this table we can see who asked to be friend and if it was accepted ...

The query I m trying to realize would permit to list all the user's friends from his ID and also return the friendship statut (accept field ==> waiting for an answer, accepted or refused).

Speretaly, it would be something like that :

SELECT Accept, UserAsker, UserAsked
FROM askstobefriends        
WHERE UserAsker = '".$userID."' OR UserAsked = '".$userID."' ";

==> first issue : it can either be the user who asked to be friend with someone or the opposit, that why i've put and OR. After that, I d like that for everyfriend founded there's these informations :

SELECT colUserID, colUserLogin, colUserName, colUserFirstname
FROM userTable
WHERE colUserID == FRIEND

So I guess I need to do a join query, and in my join I have to be sure that I'm using the right foreign key from the asktobefriends tablefor each cases !! (once the key could be UserAsked and another time UserAsker depending on who asked to be friends :S )

Does anyone have a clue please :S ?? Thanks ;-) !!

Thanks I just didn't know how to do that :p I thought I just had to answer "was this post usefull ?" — Karly, Jun 06 '12 at 13:07

score 1 · Answer 1 · answered Jun 06 '12 at 13:35

1

You could join the tables using criteria that ensure only friends of :userID are returned. For example:

SELECT u.*, a.Accept
FROM   askstobefriends a JOIN userTable u ON (:userID, u.colUserID) IN (
         (a.UserAsker, a.UserAsked),
         (a.UserAsked, a.UserAsker)
       )

answered Jun 06 '12 at 13:35

eggyal

122,705
18
212
237

So :userID is a variable ? in php it would be $userID ? or is the :userID, the ID from the friend ? – Karly Jun 06 '12 at 13:45
@Karly: In this case, `:userID` is the user id from PHP. I put `:` instead of `$` as [prepared statements](http://stackoverflow.com/a/60496/623041) are *so* much nicer! ;) – eggyal Jun 06 '12 at 15:12

score 1 · Accepted Answer · edited Jan 05 '13 at 22:00

Your design is wrong. A User asks to be friend of another User, so "Ask_to_be_friend" is the relation, and the cardinality is many to many, so the design will looks like this:

User_User_ID is UserAsker.

User_USer_ID1 is UserAskedtobefriend

enter image description here

and the query could be like (you'll get all the users that user_user_ID Asks to be friend of):

Select U.* from User as U
Join Ask_to_be_friend as A on
U.user_ID = A.User_user_ID 
--where a.accept=1  if you add this, this will give 
--you all the friends ID of the user_ID table User

If you want to get the names or extra info of the askedtobefriend you'll need a extra Join

Select U.* from User as U
Join Ask_to_be_friend as A on
U.user_ID = A.User_user_ID
Join User as U2 on
A.User_User_ID1=u2.User_ID 
 --where a.accept=1 ,with this you'll with get only the friends

Join on a cyclic relation (beginner)

2 Answers2