6

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

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
Improve this question
7
  • -1, "show me the codez" is no better on Meta than the main site. – user154510 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 – user154510 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

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.

Improve this answer
3
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]

Improve this answer
1
  • 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
1

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

Improve this answer

You must log in to answer this question.

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