Solved

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

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

756 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