Sami Sami - 3 months ago 16
SQL Question

Fetch recordonly if it has specific tags

I have following sql query, to fetch unnoticed questions which contain no other tag than my favorite tags, plus it contains some more filters.

Live Demo

There are two major issues with this query (It might need more improvements though)


  1. I have applied a bogus technique to achieve in and only in=> (You can see the query. I had to use same query twice first to get what i need and then to filter same results with a not other than filter, to ignore all questions which have any other tag than my favorite tags). I could not find any way else to do it.

  2. I have applied
    distinct
    because it was giving me duplicate results even when i have not used any left join. How could I distinct Ids without using distinct 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

You can use the HAVING clause if the ID is the only column you need and IN() to avoid multiple conditions on the same column:

Check live demo here Response waiting 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(*) will return the total number of records for this ID , the SUM(CASE..) will return the number of records without your undesired tags. If they are equal , it means that only desired tags exists.