0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people looking to improve their interpersonal communication skills

SELECT Closers.PostId AS [Post Link], Users.Id AS [User Link], Users.Reputation

FROM
(
SELECT PostId, JSON_VALUE(Text, '$.Voters[0].Id') AS UserId
  FROM PostHistory WHERE PostHistoryTypeId = 10
UNION ALL
SELECT PostId, JSON_VALUE(Text, '$.Voters[1].Id') AS UserId
  FROM PostHistory WHERE PostHistoryTypeId = 10
UNION ALL
SELECT PostId, JSON_VALUE(Text, '$.Voters[2].Id') AS UserId
  FROM PostHistory WHERE PostHistoryTypeId = 10
UNION ALL
SELECT PostId, JSON_VALUE(Text, '$.Voters[3].Id') AS UserId
  FROM PostHistory WHERE PostHistoryTypeId = 10
UNION ALL
SELECT PostId, JSON_VALUE(Text, '$.Voters[4].Id') AS UserId
  FROM PostHistory WHERE PostHistoryTypeId = 10
) AS Closers
-- note that UserId can be NULL, if the question is closed by less than 5 people.
-- a JOIN on Users takes care of that

INNER JOIN Users
  ON Closers.UserId = Users.Id

fork query

edited mar 19 18

created mar 19 18 Glorfindel