Serendipity: match posts which contains these 2 users

1

Please login or register to vote for this query.

(click on this box to dismiss)

Think of IMDB collaborations and overlap feature: http://www.imdb.com/search/common Many, many thanks to @AakashM http://meta.stackoverflow.com/a/253247/274502

Stack Overflow

Q&A for programmers

declare @userId1 int = ##userId1## -- test with 2963652;
declare @userId2 int = ##userId2## -- test with 2292723;


-- questionIds where userid1 is involved
;with user1questionid as (
-- questions
select id from posts where posttypeid = 1 and owneruserid = @userId1
union
-- answers
select parentid from posts where posttypeid = 2 and owneruserid = @userId1
union
-- comments
select postid from comments c where userid = @userId1
union
-- questions involved in otherwise
select p.id from posts p inner join posthistory ph on p.id = ph.postid
where p.posttypeid = 1
and ph.userid = @userId1
union
-- answers involved in otherwise
select parentid from posts p inner join posthistory ph on p.id = ph.postid
where p.posttypeid = 2
and ph.userid = @userId1
),
-- questionIds where userid2 is involved
 user2questionid as (
-- questions
select id from posts where posttypeid = 1 and owneruserid = @userId2
union
-- answers
select parentid from posts where posttypeid = 2 and owneruserid = @userId2
union
-- comments
select postid from comments c where userid = @userId2
union
-- questions involved in otherwise
select p.id from posts p inner join posthistory ph on p.id = ph.postid
where p.posttypeid = 1
and ph.userid = @userId2
union
-- answers involved in otherwise
select parentid from posts p inner join posthistory ph on p.id = ph.postid
where p.posttypeid = 2
and ph.userid = @userId2
),
intersectingquestion as (
select id from user1questionid 
intersect
select id from user2questionid 
)
select p.Id as [Post Link] from posts p
inner join intersectingquestion q on p.id = q.id

;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached