How to use INNER JOIN in the scenario?

Question

I have 2 tables:

'Users' Table

id      username  
----    -------- 
0001    user1          
0002    user2          
0003    user3          
0004    user4     

'Friends' Table

user_id  friend_id  friend
-------  ---------  ------
0001     0004       1
0002     0004       1
0005     0004       0

How do I display all user4 friends' name? if in friends table, friend column, 1 indicates they are friend, 0 indicate they are still not friend.

I use INNER JOIN, which looks like this:

SELECT users.username
FROM `users`
INNER JOIN `friends` ON users.id = friends.friend_id
WHERE friends.user_id = 0004
  AND friend = 1;

But what I get is:

user4 and user4 instead of user1 and user2

Can help me?

Answer 1

SELECT u.username
FROM Friends f, Users u
WHERE f.friend = 1 AND 
 ((f.friend_id = @userid AND f.user_id = u.id) OR
  (f.user_id = @userid AND f.friend_id = u.id))

Answer 2

select u.username
from friends f, users u
where f.friend_id='0004' and f.friend=1 and f.id=u.user_id;

Edit: This is the same as:

select u.username
from friends f inner join users u on f.id=u.user_id
where f.friend_id='0004' and f.friend=1;

Answer 3

SELECT users.username
FROM `users`
INNER JOIN `friends` ON users.id = friends.user_id
WHERE friends.user_id = 0004
  AND friend = 1;

Answer 4

Are you sure you don't want to link the friends table to the users table on the user_id instead of the friend_id? Then change the where clause to use the friend_id instead of the user_id. There's different ways of formatting the join, but the way your doing it using an inner join looks fine.

SELECT users.username
FROM `users` 
INNER JOIN `friends` ON users.id = friends.user_id 
WHERE friends.friend_id = 0004   
AND friend =1

Answer 5

SELECT u.username
FROM   Friends f, Users u
WHERE  f.user_id = u.id
       AND f.friend = 1
       AND f.friend_id = '0004'

Answer 6

Do you mean like this?

SELECT u.username
FROM friends AS f
INNER JOIN users AS u USING user_id
WHERE f.friend_id = 0004 AND f.friend = 1

Answer 7

select U.Username
from
    Users as U
    inner join Friends as F on U.Id = F.user_id and F.friend = 1
where
    F.friend_id = '0004'

If the friend table is just a mapping table then you not want to map both ways?

select U.Username
from
    Users as U
    left outer join
    (
     select 
      F.user_id as Id
     from
      Friends as F
     where
      F.friend_id = '0004'
     and
      F.friend = 1

    ) as Mapping1 on Mapping1.Id = U.id
    left outer join
    (
     select 
      F.friend_id as Id
     from
      Friends as F
     where
      F.user_id = '0004'
     and
      F.friend = 1

    ) as Mapping2 on Mapping2.Id = U.id

where
    Mapping1.Id is not null or Mapping2.Id is not null

Answer 8

dmcgiv has an important point that you need to think about if the friend mapping is bidirectional (i.e., if A is friends with B then B is automatically friends with A). You need to check whether the user you're interested in is on either side of the Friends table mapping.

You can either do it dmcgiv's way, or another way would be to just insert links in both directions into the Friends table when you add a friendship. For example, if user 0004 is friends with user 0006, you would insert into the Friends table:

user_id | friend_id | friend
0004    | 0006      |   1
0006    | 0004      |   1

I don't think you really need a friend column, by the way. Unless there's some reason to track "non-friends" you can just delete the mapping from the Friends table if the friendship ends.

Answer 9

select username from users us where us.id in ( select f.[user_id] from users u inner join friends f ON u.id = f.friend_id where f.friend=1)