MySQL - Complex query issue, multiple tables

Question

I'm having a bit of trouble figuring out an SQL statement that I need to make.

I have 2 tables

Friends

===================================
friendfromid | friendtoid | request
===================================

Users

=================
userid | username
=================

I need to get the userid and username of each user that is my friend.

My id is either in the friendfromid or friendtoid depending on if I am requested the user or the user requested me.

So basically what needs to happen is the script needs to look at the friends table, get all the rows where my id is either friendfromid or friendtoid check that the request field is set to 1, take all the rows that fit that match then get the ids and usernames of each friendfromid or friendtoid which isn't mine.

For instance, if my id was 8 and the friends id was 9, let's say they requested me their id would be in the friendfromid field and my id would be in the friendtoid field, that means the script would take their id (9) and match it to that user in the users table.

Somnath Muluk · Accepted Answer · 2012-01-13 04:11:23Z

2

This will give friends list with friend id and friend name:

SELECT  u.userid , u.username
FROM Friends f 
JOIN Users u 
ON (( f.friendfromid ={the ID} AND friendtoid=u.userid)
OR ( f.friendtoid = {the ID}  AND friendfromid=u.userid))
WHERE f.request = 1

Put {the ID}= user id whose friend list required.

edited Jan 13, 2012 at 4:11

answered Jan 12, 2012 at 5:08

Somnath Muluk

58.3k39 gold badges225 silver badges230 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Francis Avila Over a year ago

I think you mean (friendfromid=? AND friendtoid=userid) OR (friendtoid=? AND friendfromid=userid).

Francis Avila Over a year ago

What I mean is when fromid matches toid is the foreign key and vice-versa. Your entire join condition is equivalent to {the ID} = userid!

BigJobbies Over a year ago

@SomnathMuluk: I had to remove the double equal signs, but yes it works perfect, thank you

Somnath Muluk Over a year ago

sorry man....by mistake i had done double equal signs.... i have removed double equal signs and placed single equal answer now.

user684202 · Accepted Answer · 2012-01-12 04:26:32Z

1

Should be something like this:

SELECT * 
FROM users 
JOIN friends 
ON friendfromid != userid
OR friendtoid != userid
WHERE request = 1
  AND userid = {the ID}

answered Jan 12, 2012 at 4:26

user684202

1 Comment

Raptor Over a year ago

Incorrect. He wants to get the ID either from friendfromid or friendtoid where his user ID is on the opposite field.

Raptor · Accepted Answer · 2012-01-12 04:30:01Z

1

Can't achieve in 1 query.

To get friendtoid with "my user ID" in friendfromid field:

SELECT b.friendtoid FROM Users a, Friends b WHERE request=1 AND friendfromid = a.userid

Vice versa for friendfromid.

answered Jan 12, 2012 at 4:30

Raptor

54.4k47 gold badges254 silver badges405 bronze badges

1 Comment

BigJobbies Over a year ago

Sorry, this doesnt make any sense .... I need to get the userid and and username from the users table

Stephen Senkomago Musoke · Accepted Answer · 2012-02-27 10:52:54Z

1

Use a UNION query, joining to friends as below

SELECT username FROM user INNER JOIN friends ON (userid = friendfrom) WHERE friendto = {myid) AND request = 1
UNION
SELECT username FROM user INNER JOIN friends ON (userid = friendto) WHERE friendfrom = {myid} AND request = 1

Remember you need indexes on the friendto and friendfrom columns for performance

answered Feb 27, 2012 at 10:52

Stephen Senkomago Musoke

3,5232 gold badges32 silver badges27 bronze badges

Collectives™ on Stack Overflow

MySQL - Complex query issue, multiple tables

4 Answers 4

4 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related