Home SQL Server Create View from Two Tables
Reply: 0

SQL Server Create View from Two Tables

user1840
1#
user1840 Published in May 28, 2018, 3:20 am

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!

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO