Using data explorer, I'd like to explore, out of questions with tag1 but not with tag2, what tags are the most common.

For example, "What tags are associated with non-Rails Ruby questions?". I'm trying to look after the tagging of Ruby questions, but I was wondering which tag I should use for a certain question, and knowing what the most common tags are would help with that.

Edit: A query that lacks the ability to exclude tag2 is https://data.stackexchange.com/stackoverflow/s/1553/tags-that-are-used-together-manual-edition

   t2.TagName AS [Used Together With], COUNT(pt1.PostId) AS [Post Count]
FROM PostTags pt1
   JOIN PostTags pt2 ON pt1.PostId = pt2.PostId
   JOIN Tags t1 ON t1.TagName LIKE ##TagName:string## AND t1.Id = pt1.TagId
   JOIN Tags t2 ON t2.Id = pt2.TagId
WHERE t1.TagName <> t2.TagName
GROUP BY t1.TagName, t2.TagName
  • -1, "show me the codez" is no better on Meta than the main site.
    – user154510
    Sep 23, 2011 at 4:30
  • @MatthewRead: Thanks for explaining your downvote. Does the fact that I'm doing it to help curate the website make it different to just doing it out of idle curiosity? Sep 23, 2011 at 4:32
  • In other words -- what is the query for the related tags list when I search for [ruby] -[ruby-on-rails]?
    – agf
    Sep 23, 2011 at 4:39
  • 1
    @MatthewRead I object to that downvote ... this question is legit and may be helpful to others
    – waffles
    Sep 23, 2011 at 5:00
  • Alright, I'll remove my downvote ... but no upvote :P
    – user154510
    Sep 23, 2011 at 5:12
  • @agf: Exactly right. Sep 23, 2011 at 5:15
  • @MatthewRead: That's ok, I got the ubiquitous sympathy upvote! Sep 23, 2011 at 5:16

3 Answers 3


Is this what you are after?

select top 100
  t1.TagName as [Used Together With], count(p1.PostId) as [Post Count]
from PostTags p1
join PostTags p2
  on p2.PostId = p1.PostId and
     p2.TagId != p1.TagId and
     p2.TagId in (select t2.Id from Tags t2 where t2.TagName like ##WithTag:string##)
left join PostTags p3
  on p3.PostId = p1.PostId and
     p3.TagId in (select t3.Id from Tags t3 where t3.TagName like ##WithoutTag:string##)
join Tags t1
  on t1.Id = p1.TagId
where p3.PostId is null
group by t1.TagName
order by count(p1.PostId) desc

you can find cases where a post does not have a specified tag by performing a left join and then filtering out cases that matched in the where clause.

FROM Posts 
WHERE PostTypeId = 1 AND 
      tags LIKE '%<ruby>%' AND 
      tags NOT LIKE '%<ruby-on-rails>%'

should do it.

In search, you can use [ruby] -[ruby-on-rails]

  • That lists the posts, not the tags. I've added a query of an existing search that doesn't exclude tag2 to the question in response. Sep 23, 2011 at 5:17

Do you really need Data Explorer for that? Search [tag1] -[tag2] and have a look at the related tags listed in the sidebar.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .