CODEWITHSUNDEEP
mysqlsqldatabasemany-to-many
Feyyaz
Feyyaz

Reputation: 23

SQL select posts without specific tag

Straight to the point. I have three tables POSTS, TAGS, POST_TAGS

POSTS { p_id, title }
TAGS { t_id, name }
POST_TAGS { p_id, t_id }

One post can have multiple tags and i want to select all post that don't have a specific tag. for example take this demo data:

TASKS
p_id | title
1      MyPost 1
2      MyPost 2
3      MyPost 3

TAGS
t_id | name
1      red
2      green

POST_TAGS
p_id | t_id
1      1
2      1
2      2
3      2

Now i want to see all POSTS that do not have the TAG 'green'. My current SQL query looks like this:

SELECT DISCTINCT
   p.p_id, p.title 
FROM
   POSTS as p,
   POST_TAGS as pt
WHERE 
   pt.p_id = p.p_id AND pt.t_id != 2

but this is going to return me this

RESULT
p_id | title
1      MyPost 1
2      MyPost 2

because 'MyPost 2' also has the TAG red it is listet.

Desired result is:

RESULT
p_id | title
1      MyPost 1

EDIT: Thanks to all of you guys, i accepted GarethD answer because NOT EXISTS is more self-explanatory. NOT IN is working but not NULL save (even if i wasn't asking for it - thanks to Nico Haase as well)

GermanC solution also is correct and working, but isn't as self-explanatory as the selected answer. thanks to you too.

Upvotes: 1

Views: 531

Answers (3)

GarethD
GarethD

Reputation: 69819

You can do this using NOT EXISTS:

 SELECT p.p_id, p.title 
 FROM   POSTS AS p
 WHERE  NOT EXISTS 
        (   SELECT  1 
            FROM    POST_TAGS AS pt 
            WHERE   pt.p_id = p.p_id
            AND     pt.t_id = 2
        );

Upvotes: 4

Nico Haase
Nico Haase

Reputation: 12130

This will do the job, as it searches for all postings which are tagged with 2 in an inner query and excludes them in the outer one

SELECT DISTINCT p.p_id WHERE p.p_id NOT IN(
    SELECT DISCTINCT
       p.p_id 
    FROM
       POSTS as p,
       POST_TAGS as pt
    WHERE 
       pt.p_id = p.p_id AND pt.t_id = 2
)

Upvotes: 0

GermanC
GermanC

Reputation: 279

You can explicitely join looking for the Green tag, and show those posts where the join was not successful:

SELECT
   p.p_id, p.title 
FROM
   POSTS as p
LEFT OUTER JOIN
   POST_TAGS as pt on pt.p_id = p.p_id AND pt.t_id = 2 
WHERE 
   pt.p_id is null

Upvotes: 3

Related Questions