Finding favorites of a user with no accepted answer and no answer posted by that user


Please login or register to vote for this query.

(click on this box to dismiss)

Gardening and Landscaping Meta

Q&A about the site for gardeners and landscapers

-- UserID: Search the favorites of user ID:
-- OnlyNoAccepted: Return only questions with no accepted answer (Y/N)?
-- OnlyNotAnswered: Return only questions where above user has not answered (Y/N)?
-- VoteAge: Return only questions favorited more than ## days ago:
DECLARE @UserID int = ##UserID:int##
DECLARE @OnlyNoAccepted nvarchar = ##OnlyNoAccepted:string?N##
DECLARE @OnlyNotAnswered nvarchar = ##OnlyNotAnswered:string?Y##
DECLARE @MinFavAge int = ##VoteAge:int?15##

SET @OnlyNoAccepted = UPPER(@OnlyNoAccepted)
SET @OnlyNotAnswered = UPPER(@OnlyNotAnswered)

SELECT p.Id AS [Post Link], MAX(p.Score) AS 'Score',
    IIF(MAX(p.AcceptedAnswerId) IS NULL, 'No', 'Yes') AS 'Accepted Answer?',
    'site://a/' + CAST(MAX(a.Id) AS nvarchar(10)) + '|Link' AS 'User Answer',
    'site://q/' + CAST(MAX(a.ParentId) AS nvarchar(10)) + '|link' AS 'Answer-Parent'

FROM Posts p
LEFT JOIN Votes v ON v.VoteTypeId = 5 AND v.PostId = p.Id
LEFT JOIN Posts a ON a.PostTypeId = 2 AND a.ParentId = p.Id AND a.OwnerUserId = @UserID

WHERE p.PostTypeId = 1 AND
    (p.AcceptedAnswerId IS NULL OR @OnlyNoAccepted != 'Y') AND
    v.UserId = @UserID AND
--    ((SELECT p2.Id FROM Posts p2 WHERE p2.PostTypeId = 2 AND p2.ParentId = p.Id AND p2.OwnerUserId = @UserId) IS NULL
--      OR @OnlyNotAnswered != 'Y')
    (a.Id IS NULL OR @OnlyNotAnswered != 'Y') AND 
    DATEDIFF(day, v.CreationDate, CURRENT_TIMESTAMP) > @MinFavAge



Enter Parameters

Switch to main site
loading Hold tight while we fetch your results