Geek Answers Handbook

It looks simple, but I'm at a standstill!

It should be so simple, but I draw a space. I have two tables.

Table:article_tag_pvt
colum: article_id
column: tag_id

Table: tag
column: tag_id
column: name

A table article_tag_pvtis a pivot table of many to many.

The problem is that I need a query that specified a list of tag names that will retrieve article IDs that ONLY match those tag names. The main join of these tables is as follows:

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

I already have a query that will retrieve article IDs that match ANY tag names. It 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 tried this, but without 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

0
source share
2 answers

, , , . , GROUP BY HAVING .

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
+3

:

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'

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

COUNT (DISTINCT t.name) , t.name IN.

+2

More articles:


All Articles