0

Please login or register to vote for this query.

(click on this box to dismiss)

Shows all questions on which both users have answers (or one of them is question owner), or the question or one of its answers have a comment interaction between the two

Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Italian language

-- Who have I interacted with?
-- Shows all questions on which both users have answers (or one of them is question owner), or the question or one of its answers have a comment interaction between the two

with me as
(
    select ISNULL(p.ParentId, c.PostId) as QuestionId, c.PostId as PostId, 1 as IsComment from Comments c left join Posts p on (c.PostId = p.Id) where c.UserId = ##MyUserId##
    union select ISNULL(p.ParentId, p.Id) as QuestionId, p.Id as PostId, 0 as IsComment from Posts p where OwnerUserId = ##MyUserId##
), them as
(
    select ISNULL(p.ParentId, c.PostId) as QuestionId, c.PostId as PostId, 1 as IsComment from Comments c left join Posts p on (c.PostId = p.Id) where c.UserId = ##TheirUserId##
    union select ISNULL(p.ParentId, p.Id) as QuestionId, p.Id as PostId, 0 as IsComment from Posts p where OwnerUserId = ##TheirUserId##
)
select me.QuestionId as [Post Link] from me inner join them on (
    (me.IsComment = 0 and them.IsComment = 0 and me.QuestionId = them.QuestionId)
    or
    ((me.IsComment = 1 or them.IsComment = 1) and me.PostId = them.PostId)    
) group by me.QuestionId

fork query

created aug 23 11 balpha