CODEWITHSUNDEEP
phpmysql
Bhavyanshu
Bhavyanshu

Reputation: 536

SQL query for fetching friend list

I need help with SQL query.

I have two tables. One is users and other one is userfriends

users table:
aid email                    firstname 
1   [email protected]         example
2   [email protected]        example2
3   [email protected]        example3
4   [email protected]        example4

userfriends tables:
reqid     email                    friendemail               status 
1         [email protected]        [email protected]            1       (example1 is frnds with example2)
2         [email protected]        [email protected]            2       (example2 request pending)
3         [email protected]        [email protected]            1       (example1 is frnds with example3)
4         [email protected]        [email protected]            1       (example1 is frnds with example4)

So when status is 2 the add request is pending and at status 1 they are friends. What i want is that i want to retrieve the complete friendlist for user example1. I want to pull out names from users table for corresponding output from previous query to display as friendlist.

Upvotes: 0

Views: 757

Answers (3)

Landon
Landon

Reputation: 4108

I think you guys are missing the fact that the searched for email could be in either column:

select u.firstname
from userfriends f, users u where 
u.email='[email protected]' and f.status=1 and
(u.email = f.email and f.friendsemail='[email protected]')
or
(u.email = f.friendsemail and f.email='[email protected]')

Upvotes: 1

juergen d
juergen d

Reputation: 204746

select distinct friendemail
from userfriends f
inner join users u on u.email = f.email
where f.status = 1
and u.firstname = 'example'

Upvotes: 1

zmbq
zmbq

Reputation: 39013

You need to JOIN both tables:

SELECT users.firstname 
    FROM userfriends UF 
    JOIN users U ON (U.email=UF.friendemail)
WHERE UF.email='your user'

Upvotes: 0

Related Questions