Noobie SQL INNER JOIN question

what are the table structures of your tables: Users and Friends ?

AW

do you in fact havce two tables (User and Friend)?

AW

From the looks of it you have a main user table (User) which containts User info + a pointer to their Image in the Image table.

Now if you want to just display the user's friends only then
SELECT * from Friend A
      left JOIN user B ON A.FriendId = B.UserId
      LEFT JOIN Image C on B.MainImgId = C.ImageId
  WHERE A.UserId = @userid;

You don't need to friend in the where clause and I would swap round the left join on user for completeness.

Now if you wanted to display the friends list of the user's friends you would have to traverse the table like a bill of materials.

First, user and image are reserved words in SQL Server - so I added brackets [ ] around the table names as shown below
Next, I moved the [User] table to the top of the join list as the base table (assuming they should always exist in the [User] table)
Then, I modifed the where clause to join to B.UserId ([User] table)  (assuming the UserId was not in the Friend table - FriendId = UserId)
Also, removed FriendId from the WHERE clause.

This seems to work:

DECLARE @USERID INT
SET @USERID = 6

SELECT *
FROM [User] B
LEFT OUTER JOIN [Friend] A ON B.UserId = A.FriendId
LEFT OUTER JOIN [Image] C ON B.MainImgId = C.ImageId
WHERE B.UserId = @userid

I'm not sure if I explained my problem correctly.

So lets say
UserID 7  = Jenni
UserId 9  = Samantha
UserId 14 = Ryan

I run this query to determine who is friend's with Jenni. If Jenni added Samantha and then Ryan adds Jenni the results look like this:

UserID   FriendID
  7             9
  14           7

So, when I show this persons friends I am pulling the "FriendId". the method I am currently using will show Jenni's friends as Samantha and Jenni.

Thats not right. Jenni's friends are Samantha and Ryan. Now, I could create two records for each friend relationship. But that seems redundant. I am using this with ASP.NET so maybe there is a way to filter a dataset or something but I'd prefer to do it in the query.

IF you wanna see what the friend system looks like here is somethign like it:

http://myspace.com/jenniquick

Ok, so maybe this isin't a 'noob' question. I'm increasing the points :)

-- Jenni

-- The unstated requirement is that a user's friend is not only his/her stated
-- friends, but also anyone who claims the user as a friend.
-- You may need to stop using the * and specify columns, since the UNION
-- requires the same number and type of columns in the results set.
-- Also note that UNION removes dupes by default, which is what you want
SELECT * from Friend A
      left JOIN user B ON A.FriendId = B.UserId
      LEFT JOIN Image C on B.MainImgId = C.ImageId
  WHERE A.UserId = @userid;
UNION  
SELECT * from user B
      left JOIN Friend A ON A.FriendId = B.UserId
      LEFT JOIN Image C on B.MainImgId = C.ImageId
  WHERE b.UserId = @userid;

Ah sooooo cool! I haven't used the union clause before. Thats rad, it works perfect! Thanks so much.

OK, I'm getting some different results now that I plugged it into my stored procedure. I am still getting an unexpected result set. Here is the query after I altered it from your original:

SELECT B.userid,B.username,B.mainImgId,B.Quote,
      A.userId,A.FriendId,A.sortOrder,A.status,A.dateAdded,A.friendType,
      C.imageSrc,C.ImageText
      FROM wisetopic_Friend A
      left JOIN wisetopic_user B ON A.FriendId = B.UserId
      LEFT JOIN wisetopic_Image C on B.MainImgId = C.ImageId
   WHERE A.UserId = @userid
UNION  
SELECT B.userid,B.username,B.mainImgId,B.Quote,
      A.userId,A.FriendId,A.sortOrder,A.status,A.dateAdded,A.friendType,
      C.imageSrc,C.ImageText
      FROM wisetopic_User B
      left JOIN wisetopic_Friend A ON A.FriendId = B.UserId
      LEFT JOIN wisetopic_Image C on B.MainImgId = C.ImageId
  WHERE b.UserId = @userid

When I execute the QUERY with the ID of 7 it returns only rows with '7'. I think i have something backwards?? :S


here is the whole friend table results:

userId      friendId    
----------- -----------
9           7          
8           7          

(2 row(s) affected)

And the result of the stored proc i changed from your suggestions:

userid      username          
----------- ---------------
7           ~ Jenni ~    
7           ~ Jenni ~    

(2 row(s) affected)


Thank you!!!

Don't worry JenniQ.  I'll help you through it until it works.

  -- Bob

I didn't test it, so let me know if it causes any trouble.

Try this:

You may have to list the exact field in the select list.  
The distinct will remove duplicates and the b.user_id <> @userid will remove the user


SELECT distinct * from Friend A
left JOIN user B ON B.UserId = A.FriendId
LEFT JOIN Image C on B.MainImgId = C.ImageId
WHERE (A.UserId = @userid OR A.FriendID = @UserID)
AND b.user_id <> @userid

Bob,

Thank you very much for taking the time to help answer my question. With a little elbow grease this query did work perfectly! You really went above and beyond to answer my question, I wish there was a rating above Excellent!!!

Just Wonderign.... Is using brackets the same as saying AS?

-- Jenni