Select an entry only if it has certain tags

Question

I have the following sql query to get discreet questions that do not contain a different tag than my favorite tags, plus it contains some more filters.

Live demo

There are two main problems in this request (more improvements may be needed)

• I applied a fake technique to achieve inside and only in => (you can see the query. I had to use the same query twice to get what I needed and then filter the same results with a filter other than the filter, ignore all questions that have any other tag than my favorite tags). I could not find another way to do this.
• I applied distinctbecause it gave me duplicate results , even if I did not use any left join. How can I distinguish identifiers without using a separate keyword

---

Select distinct top 100  
'http://stackoverflow.com/questions/'+Cast(p.Id as varchar(20)) as ids
from Posts p
Join posttags pt on p.Id=pt.PostId

where AcceptedAnswerId is null
and AnswerCount = 0
and len(body) <2000
and viewCount<30
and DateDiff(hour, p.creationDate, GETDATE())<200
and ClosedDate is null

and p.id not in

(
select p.id as id from posts p join posttags pt
on p.Id=pt.PostId
where pt.tagId != 21 and pt.tagId != 3
and pt.tagId != 9 and pt.tagId != 5
and pt.tagId != 820 and pt.tagId != 2
and pt.tagId != 22 and pt.tagId != 1508
and pt.tagId != 46426 and pt.tagId != 96
and pt.tagId != 363

and AcceptedAnswerId is null
and AnswerCount = 0
and len(body) <3000
and viewCount<30
and DateDiff(hour, p.creationDate, GETDATE())<200
and ClosedDate is null
)
order by ids

--21 mysql --3 javascript --9 c# --5 php --820 jquery
--2 html --22 sql --1508 json --46426 nodejs--96 asp.net
--363 ajax

Answer 1

You can use a clause HAVINGif it ID's the only column you need and IN()to avoid multiple conditions in one column:

Check out the demo here Answer Answer Questions

Select distinct top 100  
       'http://stackoverflow.com/questions/'+Cast(p.Id as varchar(20)) as ids
from Posts p
Join posttags pt 
 on p.Id=pt.PostId
where AcceptedAnswerId is null
    and AnswerCount <3
    and len(body) <2000
    and viewCount<30
    and DateDiff(hour, p.creationDate, GETDATE())<200
    and ClosedDate is null
GROUP BY 'http://stackoverflow.com/questions/'+Cast(p.Id as varchar(20))
HAVING COUNT(*) = SUM(CASE WHEN tagID IN(21,3,9,820,2,22,1508,46426,96,363) THEN 1 ELSE 0 END)

COUNT(*) ID, SUM(CASE..) . , , .