How to use INNER JOIN in the scenario?
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?
SELECT u.username
FROM Friends f, Users u
WHERE f.user_id = u.id
AND f.friend = 1
AND f.friend_id = '0004'
SELECT users.username
FROM `users`
INNER JOIN `friends` ON users.id = friends.user_id
WHERE friends.user_id = 0004
AND friend = 1;
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
下一篇: 如何在场景中使用INNER JOIN？