Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How can I merge two columns to create a single unique recordset (linq or SP)...

Posted on 2010-11-08
3
Medium Priority
?
369 Views
Last Modified: 2013-11-11
... and exclude the search parameter from the results?

I am trying to create a friends list.

I have a MemberMember table, where there are two columns.

One column is the person who requested friendship. The other column is the respondee.

Sometimes the user is the requestee and sometimes they are the respondee so their ID can end up being in both columns. If a user wants to see his friends, I need a single set of records with unique friends only.

My MemberMember table has the following columns that need to be merged into unique result set.

FromMemberId
ToMemberId


FYI, I would be okay doing this as a stored proc or in LINQ.... something like this?


SELECT CASE WHEN (a.FromMemberId = @UserId) THEN a.ToMemberId ELSE a.FromMemberId END AS UserId, MemberMemberId,
UserName, FirstName + ' ' + LastName as friendfullname, ProfileImage as friendProfilePhoto, CreatedOn as dateRequestMade
FROM pv_MemberMember a
INNER JOIN aspnet_Users b ON a.FromMemberId = b.UserId
INNER JOIN pv_Member c on b.UserId = c.UserId
WHERE a.FromMemberId = @UserId OR a.ToMemberId = @UserId AND a.IsApproved = 1 AND a.IsDeleted = 0 This is showing my 'friend' table.
0
Comment
Question by:Rob Gaudet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 11

Accepted Solution

by:
Larissa T earned 2000 total points
ID: 34087201

try this
I split it in 2 queries so you can better understand logic
--'my friends',
SELECT a.FromMemberId myID
, toMember, UserName, FirstName + ' ' + LastName as friendfullname, ProfileImage as friendProfilePhoto, CreatedOn as dateRequestMade
FROM pv_MemberMember a
INNER JOIN aspnet_Users b ON a.toMember = b.UserId
INNER JOIN pv_Member c on b.UserId = c.UserId
WHERE a.FromMemberId = @UserId   AND a.IsApproved = 1 AND a.IsDeleted = 0
union
      -- 'I am friend of'
SELECT a.ToMemberId myID
, FromMemberId, UserName, FirstName + ' ' + LastName as friendfullname, ProfileImage as friendProfilePhoto, CreatedOn as dateRequestMade
FROM pv_MemberMember a
INNER JOIN aspnet_Users b ON a.FromMemberId = b.UserId
INNER JOIN pv_Member c on b.UserId = c.UserId
WHERE a.ToMemberId = @UserId   AND a.IsApproved = 1 AND a.IsDeleted = 0
0
 

Author Comment

by:Rob Gaudet
ID: 34087520
Tro. Appears to work, I'll test with more recs and advise if I have any problems. Thanks.
0
 

Author Closing Comment

by:Rob Gaudet
ID: 34089355
This was exactly what I needed, thanks.
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question