?
Solved

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

Posted on 2010-11-08
3
Medium Priority
?
365 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

Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

762 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