What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 134 Stack Exchange communities.

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 http://data.stackexchange.com/stackoverflow/s/1553/tags-that-are-used-together-manual-edition

SELECT TOP 1000
   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
ORDER BY COUNT(pt1.PostId) DESC
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
1  
@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

3 Answers 3

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
SELECT * 
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

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

You must log in to answer this question.

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