How to select documents in SQL with at least N similar foreign keys

Question

 doc.id|tag.id|taggings.id|    name

  3 |  3 |  3 | heroku
  3 |  4 |  4 | javascript
  3 |  5 |  5 | html
  4 |  4 |  6 | javascript
  4 |  3 |  7 | heroku
  4 |  5 |  8 | html
  4 |  6 |  9 | swagger

I have this table

I want to select elements that have at least N of the same tags,

so lets say documents that are tagged with html and heroku.

I would want to return doc 3 and doc 4 [in this table it would be the only two things in the table. lol but still!]

Answer 1

Here's one way with count and case:

select id
from documents
group by id
having count(case when name = 'html' then 1 end) > 0
   and count(case when name = 'heroku' then 1 end) > 0

• SQL Fiddle Demo

Answer 2

Generally the scope would be in format:

class Doc
   has_and_belongs_to_many :tags

   scope :tagged_with(*list), -> {|list| joins(:tags).merge(Tag.named(*list)) }
end

class Tag
   has_and_belongs_to_many :docs

   scope :named(*list), -> {|list| where(name: list) }
end

I'm not sure that where(name: list) will work properly, so just replace the scope with arel.

scope :named(*list), -> {|list| where(arel_table[:name].in(list)) }

Usage:

Doc.tagged_with(:html, :heroku)

Answer 3

select doc_id
from mytable
where name in ('html','heroku')
group by doc_id
having count(distinct name) = 2

Answer 4

As I understand it:

SELECT doc_id, count(*) AS ct
FROM   doc_tag
WHERE  tag_id IN (3,4,5)  -- "the same tags"
GROUP  BY 1
HAVING count(*) >= 3;     -- at least N of those

SQL Fiddle.