SQL Joining two tables

BlueBallons

Hi i am trying to Join two sql tables. Table 1 is called account and it consits of account_id, fname, lname, picture and Table 2 is called friendpending and consits of account_id, friend_id and message. Im making a program that allows users to add friends, i can pass in the account_id into friendpending and it returns the friend_id's. How can i join both tables so instead of returning the friend_id it returns the first and last name connected to that id.

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;



p_account_id is the account_id passed in by the user.

GreeBo

BlueBallons wrote: »

Hi i am trying to Join two sql tables. Table 1 is called account and it consits of account_id, fname, lname, picture and Table 2 is called friendpending and consits of account_id, friend_id and message. Im making a program that allows users to add friends, i can pass in the account_id into friendpending and it returns the friend_id's. How can i join both tables so instead of returning the friend_id it returns the first and last name connected to that id.

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;



p_account_id is the account_id passed in by the user.

I would think a little more about your schema as I'm not sure how extensible it will be. POssibly consider a third table.
Whats your long term plan for tracking friends since the 2nd table is currenlty called "pendingFriends" I'm assuming data there moves on once accepted?


You will need to now use the friend_id to query the account table to get their info, this could be a separate query based on the results of the first, or possibly a sub query such as
select name from accounts where account_id in (your current statement)

BlueBallons

Hi i have got it working but thanks anyway for your reply. This is not going to be used online it is just a learning exercise! Luckily i figured it out!