CODEWITHSUNDEEP
mysqlsql
Krimson
Krimson

Reputation: 7664

Constructing query that contains a mapping table?

I am building a very simple cms.
The two objects in it are articles and tags Here is the db schema i have currently:

The scheema ERD

Using this I can get a list of articles by tags SELECT ... WHERE web_tag.Name = 'News'

Question

How can I get a list of articles which DONOT contain a tag.

I tried doing

SELECT ... WHERE web_tag.Name != 'News'

This work if the article has only one tag. It won't work if the article has more than one tag obviously

Thanks a bunch!

Upvotes: 0

Views: 209

Answers (2)

Jose Emilio Cabana
Jose Emilio Cabana

Reputation: 13

another way

select ID_Articles, Name, Slug from web_article where ID_Article in (select ID_Articles from web_article except ( select ID_Article from web_tag_to_Article where ID_Tag in (select ID_Tag from web_tag where name='news') ) )

Upvotes: 0

peterm
peterm

Reputation: 92785

One way to do it

SELECT a.id_article, a.name
  FROM web_article a
 WHERE NOT EXISTS
(
  SELECT *
    FROM web_tag_to_article ta JOIN web_tag t
      ON ta.id_tag = t.id_tag
   WHERE id_article = a.id_article
     AND t.name = 'News'
)

or

SELECT id_article, name
  FROM web_article
 WHERE id_article NOT IN
(
  SELECT id_article
    FROM web_tag_to_article ta JOIN web_tag t
      ON ta.id_tag = t.id_tag
   GROUP BY id_article
  HAVING MAX(t.name = 'News') = 1
)

or

SELECT a.id_article, a.name
  FROM web_article a LEFT JOIN web_tag_to_article ta 
    ON a.id_article = ta.id_article LEFT JOIN web_tag t
    ON ta.id_tag = t.id_tag
 GROUP BY a.id_article, a.name
HAVING MAX(COALESCE(t.name,'') = 'News') = 0

Here is SQLFiddle demo

Upvotes: 1

Related Questions