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

   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
share|improve this question
-1, "show me the codez" is no better on Meta than the main site. –  Matthew Read Sep 23 '11 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? –  Andrew Grimm Sep 23 '11 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 '11 at 4:39
@MatthewRead I object to that downvote ... this question is legit and may be helpful to others –  waffles Sep 23 '11 at 5:00
Alright, I'll remove my downvote ... but no upvote :P –  Matthew Read Sep 23 '11 at 5:12
@agf: Exactly right. –  Andrew Grimm Sep 23 '11 at 5:15
@MatthewRead: That's ok, I got the ubiquitous sympathy upvote! –  Andrew Grimm Sep 23 '11 at 5:16
add comment

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
add comment
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]

share|improve this answer
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. –  Andrew Grimm Sep 23 '11 at 5:17
add comment

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

share|improve this answer
add comment

You must log in to answer this question.

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