CODEWITHSUNDEEP
mysql
user2872510
user2872510

Reputation: 115

Mysql Join Users Table with Friends Table

I two tables one is a table of just Users the other is a list of friend relationships. Im trying to join the Username column of the Users Table with the column FriendID in the Friends table. Also i want the Status to equal Accepted so i added a where clause. Right now it just outputs the columns idUsers and Username but nothing in them.

SELECT idUsers, Username FROM Users INNER JOIN `Friends` on Users.Username =
Friends.FriendID WHERE Status = 'Accepted'

              Users Table

+---------+----------+----------+------------+
| idUsers | Username | Password | OrderGroup |
+---------+----------+----------+------------+
|       1 | test     | test     |       NULL |
|       2 | test2    | test     |       NULL |
+---------+----------+----------+------------+

              Friends Table

       +--------+----------+----------+
       | UserID | FriendID | Status   |
       +--------+----------+----------+
       |      1 |        2 | Accepted |
       +--------+----------+----------+

Upvotes: 0

Views: 795

Answers (2)

Adel
Adel

Reputation: 1468

Your Join should by by idUsers 

SELECT idUsers, Username FROM Users INNER JOIN Friends on Users.idUsers  =
Friends.FriendID WHERE Status = 'Accepted'

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

You have joined your table with wrong relation how a varchar username can be equal to integer id Users.Username =Friends.FriendID ,update your on clause to

ON Users.idUsers =Friends.FriendID

SELECT 
  idUsers,
  Username 
FROM
  Users 
  INNER JOIN `Friends` 
    ON Users.idUsers = Friends.FriendID 
WHERE `status` = 'Accepted'

Upvotes: 2

Related Questions