Reciprocal questions and answers between 2 users.

1

Please login or register to vote for this query.

(click on this box to dismiss)

See https://stackoverflow.com/questions/52561673/sql-find-reciprocal-relationship Also see https://data.stackexchange.com/stackoverflow/query/904096/list-users-with-reciprocal-questions-and-answers

Stack Overflow

Q&A for professional and enthusiast programmers

-- UserA: Enter ID of user A
-- UserB: Enter ID of user B
WITH possibleAnswers AS (
    SELECT
                a.Id                AS aId
                , a.ParentId        AS qId
                , a.OwnerUserId   
                , a.CreationDate
    FROM        Posts a
    WHERE       a.PostTypeId        = 2  --  answers
    AND         a.OwnerUserId       IN (##UserA:INT##, ##UserB:INT##)
),
possibleQuestions AS (
    SELECT
                q.Id                AS qId
                , q.OwnerUserId   
                , q.Tags
    FROM        Posts q
    INNER JOIN  possibleAnswers pa  ON q.Id = pa.qId
    WHERE       q.PostTypeId        = 1  --  questions
    AND         q.OwnerUserId       IN (##UserA:INT##, ##UserB:INT##)
    AND         q.OwnerUserId       != pa.OwnerUserId  --  No self answers
)
SELECT 
            pa.OwnerUserId          AS [User Link]
            , 'answers'             AS [Action]
            , pq.OwnerUserId        AS [User Link]
            , pa.CreationDate       AS [at]
            , pq.qId                AS [Post Link]
            , pq.Tags
FROM        possibleQuestions pq
INNER JOIN  possibleAnswers pa      ON pq.qId = pa.qId
WHERE       pq.OwnerUserId          =  ##UserB:INT##
AND         EXISTS (SELECT * FROM possibleQuestions pq2  WHERE pq2.OwnerUserId =  ##UserA:INT##)

UNION ALL SELECT 
            pa.OwnerUserId          AS [User Link]
            , 'answers'             AS [Action]
            , pq.OwnerUserId        AS [User Link]
            , pa.CreationDate       AS [at]
            , pq.qId                AS [Post Link]
            , pq.Tags
FROM        possibleQuestions pq
INNER JOIN  possibleAnswers pa      ON pq.qId = pa.qId
WHERE       pq.OwnerUserId          =  ##UserA:INT##
AND         EXISTS (SELECT * FROM possibleQuestions pq2  WHERE pq2.OwnerUserId =  ##UserB:INT##)

ORDER BY    pa.CreationDate

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results