1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those committed to a vegan or vegetarian lifestyle and anyone interested in learning more

CREATE TABLE #Raw (
  User1 INT NOT NULL,
  Tag1 INT NOT NULL,
  Count1 INT NOT NULL,
  User2 INT NOT NULL,
  Tag2 INT NOT NULL,
  Count2 INT NOT NULL,
  MinCount INT NOT NULL
);

WITH Raw AS (
  SELECT
    Q.OwnerUserId AS QUserId,
    A.OwnerUserId AS AUserId,
    PostTags.TagId AS TagId,
    COUNT(*) AS Count
  FROM Posts Q, PostTags, Posts A
  WHERE
    A.ParentId = Q.Id
    AND
    PostTags.PostId = Q.Id
    AND
    Q.OwnerUserId IS NOT NULL
    AND
    A.OwnerUserId IS NOT NULL
  GROUP BY PostTags.TagId, Q.OwnerUserId, A.OwnerUserId
)
INSERT INTO #Raw
SELECT TOP ##TopN:int?100##
  A.QUserId AS User1,
  A.TagId AS Tag1,
  A.Count AS Count1,
  B.QUserId AS User2,
  B.TagId AS Tag2,
  B.Count AS Count2,
  CASE WHEN A.Count > B.Count THEN B.Count ELSE A.Count END AS MinCount
FROM Raw A, Raw B
WHERE
  A.QUserId = B.AUserId
  AND
  A.AUserId = B.QUserId
  AND
  A.TagId <> B.TagId
  AND
  A.QUserId < B.QUserId
ORDER BY MinCount DESC;

SELECT
  User1 AS [User Link],
  Tag1.TagName,
  Count1,
  User2 AS [User Link],
  Tag2.TagName,
  Count2,
  #Raw.MinCount
FROM #Raw, Tags Tag1, Tags Tag2
WHERE
  #Raw.Tag1 = Tag1.Id
  AND
  #Raw.Tag2 = Tag2.Id
ORDER BY #Raw.MinCount DESC

fork query

edited jan 23

created jan 22 peterh - PLEASE DELETE THIS ACCOUNT