Solved

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

Posted on 2010-11-08
3
361 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 500 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
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.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

705 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