views:

207

answers:

2

This should be so simple, but I'm drawing a blank. I have two tables.

Table:article_tag_pvt
colum: article_id
column: tag_id

Table: tag
column: tag_id
column: name

The article_tag_pvt table is a many-to-many pivot table.

The problem is, I need a query which given a list of tag names, will retrieves article Ids which ONLY match those tag names. The basic joining of these tables looks like this:

SELECT article_id
FROM article_tag_pvt pvt
INNER JOIN tag t ON t.tag_id = pvt.tag_id

I've already got a query which will retrieve article ids which match ANY tag names specified. This looks like this:

SELECT article_id
FROM article_tag_pvt pvt
INNER JOIN tag t ON t.tag_id = pvt.tag_id
WHERE t.name IN ('events','news')

I've tried these but no joy:

SELECT article_id
FROM article_tag_pvt pvt
INNER JOIN tag t ON t.tag_id = pvt.tag_id
WHERE t.name = 'events' AND t.name = 'news'

SELECT article_id
FROM article_tag_pvt pvt
INNER JOIN (
SELECT tag_id
FROM tag
WHERE name IN ('events','news')
) AS t
ON t.tag_id = pvt.tag_id

Any help would be greatly appreciated

Dave

+2  A: 

My preference is for:

SELECT pvt.article_id
  FROM article_tag_pvt pvt
  JOIN tag t ON t.tag_id = pvt.tag_id AND t.name = 'events'
  JOIN tag t2 ON t2.tag_id = pvt.tag_id AND t2.name = 'news'

Alternative using GROUP BY/HAVING:

  SELECT pvt.article_id
    FROM article_tag_pvt pvt
    JOIN tag t ON t.tag_id = pvt.tag_id
   WHERE t.name IN ('events', 'news')
GROUP BY pvt.article_id, t.name
  HAVING COUNT(DISTINCT t.name) = 2

The COUNT(DISTINCT t.name) needs to equal the number of options you define for the t.name IN clause.

OMG Ponies
Thanks Rexem for providing these. Used Dimus' approach on your second solution, the count wasn't working quite right. The number of tags could get quite long, so decided against the multiple joins. Thanks for your help.
Dave Quested
+2  A: 

The problem is that you get ALL rows which have either event or news. If you never have more than 1 event or news in many to many table you can use GROUP BY and HAVING to solve it

SELECT article_id
FROM article_tag_pvt pvt
INNER JOIN tag t ON t.tag_id = pvt.tag_id
WHERE t.name = 'events' OR t.name = 'news' 
GROUP BY article_id HAVING count(*) = 2
dimus
Spot on, thanks for your swift response.
Dave Quested