trjhtr

Sometimes, it's not enough to know what the easy prey is. You also need to know whether you will be able to hunt it down. What good is an almost turned haskell zombie if you cannot read the functional incantations of the language? How will you as a vba guru handle a python behemoth?

That's why I present to you the tag-based zombie sniffer:

SELECT 
 q.Id AS [Post Link],
 q.Tags
FROM 
 Posts q
 INNER JOIN Posts a ON q.Id = a.ParentId
 INNER JOIN PostTags ON q.Id = PostTags.PostId
 INNER JOIN Tags ON PostTags.TagId = Tags.id
WHERE 
 q.AnswerCount > 0
 AND q.AcceptedAnswerId IS NULL
 AND Tags.TagName = '##TagName##'
GROUP BY 
 q.Id, q.Tags
HAVING 
 MAX(a.Score) >= ##MinScore:int?0##
 AND MAX(a.Score) <= ##MaxScore:int?0##

-- TagName: Tag name "as you would use in your post"
-- MinScore: Highest answer has at least votes:
-- MaxScore: Highest answer has at most votes:

You can find it on SEDE.

As it's predecessor, it looks for easy prey by default:

• questions that have at least one answer


• no answer has a score of more than 0


• the highest scored answer has a score of at least 0


• has no accepted answer

However, this one also takes a tag name so that you can search for C++ zombies or similar. This is the first time I've ever written an SQL query with three JOINs, so I have no idea whether this is considered good style. Also, I group by the q.Tags to show them in the result, but I think that's a hack and not really good style.

When using "simple" Aliases for table names in a query you should alias all the tables, not just some of them. This makes the query appear more consistent. In your query, you have a and q aliases for the two facets of the Posts table (questions and answers, it makes sense), but you should also alias the PostTags and the Tags tables to keep things consistent.

Stylistically, though, your query is otherwise neat, and reads quite well.

When querying tables, you should always offer the optimizer as much information as possible. You're cheating in your queries by ignoring the PostTypeID column (1 for questions, 2 for answers). Don't rely on inferred values like null, or non-null ParentID values to fix a query.

Further, note that you can put constant conditionals in the JOIN syntax for a table. You don't need to force these constants in to the regular where-block. I prefer putting related conditions together even if they are not strictly part of the join between tables. This is a coder-preference thing... you may disagree.

The most significant issue I have with the query is the use of the having-clause, which in my mind has always been a tool-of-last-resort in a SQL query.

Common Table Expressions (CTEs) have made this sort of complexity much simpler by allowing the aggregation in a virtual table before the actual query runs. CTE's are easier to show, than describe, so consider this query with a CTE instead:

WITH BestAnswer as (
 select ParentId, Max(Score) as Score
 from Posts
 where PostTypeId = 2
 group by ParentId
)

SELECT 
 q.Id AS [Post Link],
 q.Tags
FROM 
 Posts q
 INNER JOIN PostTags pt
 ON q.Id = pt.PostId
 INNER JOIN Tags t
 ON pt.TagId = t.id
 AND t.TagName = '##TagName##'
 INNER JOIN BestAnswer a
 ON q.Id = a.ParentId
 AND a.Score >= ##MinScore:int?0##
 AND a.Score <= ##MaxScore:int?0##
WHERE 
 q.PostTypeId = 1
 AND q.AnswerCount > 0
 AND q.AcceptedAnswerId IS NULL

-- TagName: Tag name as you would use in your post
-- MinScore: Highest answer has at least votes:
-- MaxScore: Highest answer has at most votes:

See it in SEDE here.