开发者

SQL issue - how do I select all tuples which contain all references in a second table?

Heres the deal - I have three tables here:

Companies:
ID | NAME | DETAILS

TAGS
ID | TAGNAME

TAGS_COMPANIES
COMPANY_ID | TAGID

Using a nest开发者_JS百科ed query I can retrieve all companies that are tagged by tags in a certain set i.e:

select c.* from companies c where c.id in (select t.company_id where t.tagid in (12,43,67))

The above query returns all companies that have an either tag id 12, 43 or 67 but I need to retrieve all companies who are tagged 12 AND 43 AND 67

How would I redo my query here? I'm using MySQL


Not too efficient but works:

select c.* 
from companies c 
where c.id in (select t.company_id from tags_companies t where t.tagid = 12)
and c.id in (select t.company_id from tags_companies t where t.tagid = 43)
and c.id in (select t.company_id from tags_companies t where t.tagid = 67)

Another possibility using a HAVING clause:

select c.id, c.name, c.details
from companies c join tags_companies t on c.id = t.company_id
where t.tagid in (12, 43, 67)
group by c.id, c.name, c.details
having count(distinct t.tagid) = 3


With one subquery.

select c.* 
      from companies c  
      where (c.id, 3) in 
         (select t.company_id, count(distinct t.tagid) 
                 from tags t
           where t.tagid in (12,43,67) 
             group by t.company_id)

magic number 3 means different tags count.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜