Nichs Nichs - 4 years ago 85
SQL Question

How to join two tables, using specific columns multiple times

I have the following 2 tables:

A) Tables

Following


id
fkUserName
follow_fkUserName


UserProfile


id
fkUserName
Name
Surname


B) What I am trying to do

I would like to join these two tables as per below:


id
User_fkUserName
User_Name
User_Surname
Followed_fkUserName
Followed_Name
Followed_Surname


C) My Problem:

My problem is that when I try to join through a view, I am not getting the expected result.

I would like that finally I will have this result:


Id User_fkUsername User_Name User_Surname Followed_fkUserName Followed_Name Followed_Surname
1 pbean Paul Bean mWhite Mary White


D) Code I am Using:

The below is the code that I am using for the Join:


CREATE VIEW [dbo].[vwFollowingUsers]
AS SELECT
a.Id,
b.Name User_Name,
c.Surname User_Surname,
d.Name Followed_Name,
e.Surname Followed_Surname

FROM Following a

LEFT JOIN UserProfile b ON a.fkUserName = b.fkUserName
LEFT JOIN UserProfile c ON a.fkUserName = c.fkUserName
LEFT JOIN UserProfile d ON a.follow_fkUserName = d.fkUserName
LEFT JOIN UserProfile e ON a.follow_fkUserName = e.fkUserName


E) This is what I am getting:

vwFollowingUsers


Id User_Name Followed_Name
1 Paul Mary


I was following this question

F) Updated Code

After I posted my question here I have updated my code as per below:


CREATE VIEW [dbo].[vwFollowingUsers]
AS SELECT
A.Id,
B.fkUserName AS User_fkUserName,
B.Name AS User_Name,
B.Surname AS User_Surname,
C.fkUserName AS Followed_fkUsername,
C.Name AS Followed_Name,
C.Surname AS Followed_Surname

FROM Following A
INNER JOIN UserProfile B ON A.fkUserName = B.fkUserName
INNER JOIN UserProfile C ON A.follow_fkUserName = C.fkUserName;


G) Actual Data:

This is the actual data (as requested)

Following



Id fkUsername follow_fkUserName
2 nich@gmail.com steph@gmail.com



UserProfile

 

Id UserPhoto Description fkUserName Name Surname
1 ../Files/nich.jpg test nich@gmail.com Nicholas Schembri
2 ../Files/nich.jpg null nich2@gmail.com Nicholas2 Schembri2
3 ../Files/steph.jpg null steph@gmail.com Stephania D'Anastasi



I am using Visual Studio and I am trying to create a view which will give me the results as mention in section G of my question.

Answer Source

You have to join UserProfile only 2 times 1 for fetching data for user and another for follower.

Try this:

CREATE VIEW [dbo].[vwFollowingUsers]

    AS SELECT A.id, 
              B.fkUserName AS User_fkUserName, 
              B.Name AS User_Name, 
              B.Surname AS User_Surname, 
              C.fkUserName AS Followed_fkUserName, 
              C.Name AS Followed_Name,
              C.Surname AS Followed_Surname,
         FROM Following A 
         LEFT JOIN UserProfile B ON A.fkUserName = B.fkUserName 
         LEFT JOIN UserProfile C ON A.follow_fkUserName = C.fkUserName;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download