SQL Server INNER JOIN multiple internal joins with multiple relationships

Question

I have the following query. It works fine, but I need to pull the BUserName from another table named FB, which has a UserID field that is associated with the UserID in the FU table. Is it possible?

SELECT TOP 100 FF.XID, FF.YID, FF.Title, FF.FileName, FF.FilePath, FU.UserName as FUUserName, FU.UserName as BUserName FROM FF INNER JOIN FU ON FU.UserID = FF.UserID 

Just to clarify. I do not have a UserName column in the FB table. This one has an FB.UserID that is related to FF.UserID where I want to pull the second UserName. Therefore, with this relationship, I am trying to pull the username from the FF.UserID table, which is associated with the user ID in the FB table. Does that make sense?

Answer 1

You want something like this:

 SELECT TOP 100 FF.XID, FF.YID, FF.Title, FF.FileName, FF.FilePath, FU.UserName as FUUserName, FU.UserName as BUserName, FB.BUserName as FB_BUserName FROM FF INNER JOIN FU ON FU.UserID = FF.UserID INNER JOIN FB ON FB.UserID = FU.UserID 

Now the FF binds to the FU , which then binds to the FB . Since they are all connected to each other, you can use the law of association to understand that it acts as if they are all connected to each other.

  FF = FU FU = FB Therefore FF = FB 

Answer 2

Like this?

 SELECT TOP 100 FF.XID, FF.YID, FF.Title, FF.FileName, FF.FilePath, FU.UserName as FUUserName, FU.UserName as BUserName, FB.BUserName FROM FF INNER JOIN FU ON FU.UserID = FF.UserID INNER JOIN FB ON FU.UserID = FB.UserID 

Answer 3

 SELECT TOP 100 FF.XID, FF.YID, FF.Title, FF.FileName, FF.FilePath, FU.UserName as FUUserName, FB.UserName as BUserName FROM FF INNER JOIN FU ON FU.UserID = FF.UserID INNER JOIN FB ON FU.UserID = FB.UserID 

Answer 4

  SELECT TOP 100 FF.XID, FF.YID, FF.Title, FF.FileName, FF.FilePath, FU.UserName as FUUserName, FB.BUserName FROM FF INNER JOIN FU ON FU.UserID = FF.UserID INNER JOIN FB ON FB.UserID = FU.UserID 

Answer 5

Two more examples

1- with zero result:

 use my_DB SELECT tbl_users.UserName ,Updated.UserName /*Updated not in tbl*/ FROM [my_DB].[dbo].[my_tbl] left outer join tbl_users ON tbl_users.UserID = my_tbl.UserID left outer join tbl_users Updated ON tbl_users.UserID = my_tbl.LasUpdatedUserID 

2- with zero result:

 SELECT tbl_users.UserName ,Updated.UserName /*Updated not in tbl*/ FROM [my_DB].[dbo].[my_tbl] INNER JOIN tbl_users ON tbl_users.UserID = my_tbl.UserID INNER JOIN tbl_users Updated ON tbl_users.UserID = my_tbl.LasUpdatedUserID 

**