Home SQL Server Create View from Two Tables
Reply: 4

SQL Server Create View from Two Tables

potorik
1#
potorik Published in 2018-02-13 19:08:32Z

I have two tables in a SQL Server database that keeps track of a social network. First is the friends table which looks like

userId | friendId | acceptDate
==============================
   1   |     8    | 2018-01-20
   8   |     12   | 2017-11-20
   12  |     1    | 2017-12-18

So each pair is a uniqe combination, and the order is just determined by which user initiated the connection.

The second table is a table with user information

userId | name | email | ...
   1   | John  | john@example.com  | ...
   2   | Bill  | bill@example.com  | ...
   3   | Cathy | cathy@example.com | ...

I want to create a view where I can easily grab the data I need for a particular user's friends with minimal server side processing. So essentially I want to create a View where the data would look like:

userId | friendID | friendName | friendEmail | acceptDate | ....
   1   |     8    |  8's Name  |  8's Email  | 2018-01-20 |...
   1   |    12    |  12's Name |  12's Email | 2017-12-18 |...
   8   |    12    |  12's Name |  12's Email | 2017-11-20 |...
   8   |     1    |  1's Name  |  1's Email  | 2018-01-20 |...
  12   |     1    |  1's Name  |  1's Email  | 2017-12-18 |...
  12   |     8    |  8's Name  |  8's Email  | 2017-11-20 |...

Basically, it will create two rows from each friendship pair that exists in the friends table (one in the existing order, and one flipped), and will join the friend's information from the user table. This way, I can simply query the view to find me all of a specific user's friends, and it'll return the rows of only that user's friends with their info and I won't have to do any processing to sort things out.

Any help would be greatly appreciated.

Thanks!

Tab Alleman
2#
Tab Alleman Reply to 2018-02-13 19:53:09Z

Here's a way this could be done with JOINs:

SELECT u.UserID, fi.UserID AS FriendID, fi.Name, fi.email, f.acceptdate
FROM UserInfo u
INNER JOIN Friends f
  ON u.UserID=f.UserID
  OR u.UserID=f.FriendID
INNER JOIN UserInfo fi
  ON (fi.UserID=f.UserID AND u.UserID=f.FriendID)
  OR (fi.UserID=f.FriendID AND u.UserID=f.UserID)
ORDER BY u.UserID, fi.UserID
Sean Lange
3#
Sean Lange Reply to 2018-02-13 19:19:10Z

Because you have to switch all the data in the first two columns you are going to need to double up the amount data. This is probably easiest using a UNION ALL. Something like this should be pretty close.

select *
from
(
    select UserID
        , FriendID
        , FriendName = sot.name
    from SomeTable st
    join SomeOtherTable sot on sot.UserID = st.UserID

    UNION ALL

    select FriendID
        , UserID
        , FriendName = sot.name
    from SomeTable st
    join SomeOtherTable sot on sot.UserID = st.FriendID
) x
order by x.UserID
    , x.FriendID
StrayCatDBA
4#
StrayCatDBA Reply to 2018-02-13 19:19:47Z
-- Friends I accepted 
select userid, f.userId friendID, f.name friendName, f.email friendEmail, friends.acceptDate
from users inner join friends on users.userId = friends.userId inner join users f on friends.friendId = f.Userid

union all
-- friends who accepted me. 
select userid, f.userId friendID, f.name friendName, f.email friendEmail, friends.acceptDate
from users inner join friends on users.userId = friends.friendID inner join users f on friends.userid = f.Userid
Random_User
5#
Random_User Reply to 2018-02-13 19:54:35Z

Here is a working example:

Edit, I've added the email from the userID, and added the union all to support the ops heart, happy v-day.

declare @user as table (
    userID       int identity(1,1) not null primary key clustered
,   Name_        nvarchar(255) not null
,   EmailAddress nvarchar(255) not null
);

declare @duo as table (
    duoID   int identity(1,1) not null primary key clustered 
,   userID  int not null
,   loserID int not null
,   LoveDate date not null
);

insert into @user (name_, EmailAddress)

select 'bob', 'bob@example.com' union all
select 'tom', 'tom@example.com' union all
select 'nick', 'nick@example.com' union all
select 'Random', 'awesomesauce@example.com';

insert into @duo (userid, loserid, LoveDate)
select 1, 2, '2018-01-14' union all
select 1, 3, '2018-02-14' union all
select 1, 4, '2018-01-15' union all
select 2, 3, '2018-01-14' union all
select 2, 4, '2018-01-18' union all
select 3, 4, '2018-01-13';


select a.UserID
     , a.Name_ Person
     , a.EmailAddress
     , c.userID LoserID
     , c.Name_ Loser
     , c.EmailAddress 'Loser''s Email'
     , b.LoveDate
  from @user a
  join @duo b
    on a.userid = b.userid
  join @user c
    on b.loserid = c.userid

union all

select c.userID 
     , c.Name_ Person
     , c.EmailAddress
     , a.UserID LoserID
     , a.Name_ Loser
     , a.EmailAddress 'Loser''s Email'
     , b.LoveDate
  from @user a
  join @duo b
    on a.userid = b.userid
  join @user c
    on b.loserid = c.userid
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.407448 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO