Help With Sql Select Syntax

Here he comes to confuse his day! (should be my handle from now on) Im confused and very much lost. I need to figure out how to make a select statement for getting FriendID to rela

Solution 1:

select u.FirstName, u.SecondName, p.picturePath
from User u
join Friends f
  on f.FriendId = u.UserId
join Pictures p
  on p.UserId = u.UserId
where f.UserId = SessionId ( <-- sessionId is your id)

Solution 2:

SELECT u.firstName, u.secondName, p.picturepath
FROM User as u, Friends as f, Pictures as p
WHERE f.UserID = $sessionID 
AND u.UserID = f.FriendID
AND p.UserId = f.FriendID

Solution 3:

do like this :

SELECT User.*, Pictures.PicturePath
           FROM (UserINNERJOIN Friends 
                ON User.UserId = Friends.FriendId) tableINNERJOINON table.UserId = Pictures.UserId
           WHERE Friends.UserId =1// this is your id (SessionId)

this will return full information of your friends

Solution 4:

If I understand your question and data model correctly, what you are trying to do is to query a many-to-many recursive relationship between users which are connected by being friends to each other (i.e, through the Friends table which acts like the intersecting entity in a many-to-many relationship).

You can do this by joining the User table in your query twice with 2 different aliases, like this:

select 
    u2.UserID,
    u2.FirstName,
    u2.SecondName,
    p.picturepath
fromUser u1 -- This gets mejoin Friends f on u1.UserID = f.UserID -- This gets my friendsjoinUser u2 on f.FriendID = u2.UserID -- This gets my friends infojoin Pictures p on p.UserID = u2.UserID -- This gets my friends picswhere u1.UserID =1-- ...or whatever; don't actually hardcode "1"!

Solution 5:

SELECT x.FirstName,
           x.SecondName,
           x.Email,
           z.picturepath
      FROM ( Friends AS x,
             User AS y
           )
 LEFT JOIN Pictures AS z
        ON x.FriendID = z.UserId
     WHERE x.UserID = 1  # <- this is determined by the session ID
       AND x.FriendID = y.UserID