1

Please login or register to vote for this query.

(click on this box to dismiss)

Questions at which the two users met One of the following cases happenned: - one posted question, the other one answer - both posted two concurrent answers - one commented one the other one's post - both commented on the same post So we don't involve cases when e.g. one user comments answer on the other user's question.

Q&A for users of Linux, FreeBSD and other Un*x-like operating systems

/* Questions at which the two users met

One of the following cases happenned:
- one posted question, the other one answer
- both posted two concurrent answers
- one commented one the other one's post
- both commented on the same post

So we don't involve cases when e.g. one user comments answer on the other user's question.

*/
select Q.Id as [Post Link], 
       T.Id
from (
-- Where did I meet this user: only only posts
select Q.Id
from Posts as Q
    left join Posts as A on A.ParentId = Q.Id
    left join Posts as A2 on A2.ParentId = Q.Id
where Q.PostTypeId = 1 and A.PostTypeId = 2 and A2.PostTypeId = 2
    and (
        (A.OwnerUserId = ##UserId## and Q.OwnerUserId = ##OtherUser##) or
        (Q.OwnerUserId = ##UserId## and A.OwnerUserId = ##OtherUser##) or
        (A.OwnerUserId = ##UserId## and A2.OwnerUserId = ##OtherUser##)    
    )
group by Q.Id
union
-- Where did I meet this user: only comments
select *
from
(select 
    (CASE WHEN PostTypeId = 2 THEN ParentId ELSE P.Id END) as Id
from Posts as P
    join Comments as C1 on C1.PostId = P.Id
    left join Comments as C2 on C2.PostId = P.Id
    where (P.PostTypeId = 1 or P.PostTypeId = 2)
    and (
        (P.OwnerUserId = ##UserId## and C1.UserId = ##OtherUser##) or 
        (P.OwnerUserId = ##OtherUser## and C1.UserId = ##UserId##) or 
        (C1.UserId = ##UserId## and C2.UserId = ##OtherUser##)
    )
) as t
group by Id
) as t
join Posts as Q on t.Id = Q.Id

fork query

edited jun 29 13

created jun 29 13 Tomas