0

Please login or register to vote for this query.

(click on this box to dismiss)

Get all the questions where you had some interaction with another user

Q&A for pro webmasters

-- Relationship with other user
-- Get all the questions where you had some interaction with another user

declare @UserId int = ##UserId##;
declare @Other int = ##OtherUser##;
declare @result table (id int);

insert @result 
SELECT DISTINCT(q.id) as id
FROM Posts a1
JOIN Posts q 
     ON q.ID = a1.ParentID
WHERE 
    (a1.OwnerUserID = @UserId 
    AND q.OwnerUserID = @Other )
    OR 
    (q.OwnerUserID = @UserId 
    AND a1.OwnerUserID = @Other );

insert @result 
SELECT DISTINCT(p.parentId) as id
FROM Posts p
JOIN Comments c 
     ON c.PostId = p.Id
WHERE 
    (p.OwnerUserID = @UserId 
    AND c.UserId = @Other )
    OR 
    (p.OwnerUserID = @Other 
    AND c.UserId = @UserId )

insert @result 
SELECT DISTINCT(a.parentId) as id
FROM Comments c1
JOIN Comments c2 
     ON c1.PostId = c2.PostId
JOiN Posts a
     ON a.ID = c1.PostId
WHERE 
     c1.ID != c2.ID AND
    (
      (c1.UserId = @UserId 
      AND c2.UserId = @Other )
      OR 
      (c1.UserId = @Other 
      AND c2.UserId = @UserId )
    )


SELECT distinct(id) as [Post Link] FROM @result 

fork query

created jul 22 11 Yochai