CODEWITHSUNDEEP
phpmysqlsql
Blue
Blue

Reputation: 73

SQL Join returning wrong result

I am trying to join two tables. Table 1 is called account and it consists of account_id, fname, lname, picture and Table 2 is called friendpending and consists of account_id, friend_id and message. I'm making a program that allows users to add friends, when I pass in the account_id it returns that users name I need it to return the friend_id name.

This is what i have tried so far: 

SELECT account.fname, account.lname, account.picture
from account
INNER JOIN friendpending ON account.account_id = friendpending.account_id
WHERE friendpending.account_id = p_account_id;

Upvotes: 1

Views: 50

Answers (2)

Andy G
Andy G

Reputation: 19367

If you want the friend's name from the friendpending table then you need to select that field (or fields):

SELECT friendpending.name, 
    account.fname, account.lname, account.picture 
FROM account INNER JOIN friendpending ON account.account_id = friendpending.account_id
WHERE friendpending.account_id = p_account_id;

or 

SELECT friendpending.fname, friendpending.lname, account.picture  
FROM account INNER JOIN friendpending ON account.account_id = friendpending.account_id
WHERE friendpending.account_id = p_account_id;

Or, based on the comment below, ON account.account_id = friendpending.friend_id.

Upvotes: 1

Jaime Slater
Jaime Slater

Reputation: 43

Whatever you are wanting to be returned needs to be included in the SELECT part of the query.

Try something like this:

SELECT friendpending.friend_id, 
friendpending.name, 
account.fname,
account.lname,
account.picture 
FROM account
INNER JOIN friendpending ON account.account_id = friendpending.account_id
WHERE friendpending.account_id = p_account_id;

Upvotes: 0

Related Questions