CODEWITHSUNDEEP
mysqlmysql-5.7
user15125190
user15125190

Reputation: 13

Exclude items from join table

CREATE TABLE `files`
(
    `file_id`  bigint(20) unsigned             NOT NULL AUTO_INCREMENT,
    `filename` text COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`file_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

INSERT INTO `files` (`file_id`, `filename`)
VALUES (100, 'file_100.ext'),
       (101, 'file_101.ext');

CREATE TABLE `files_tags`
(
    `file_id` bigint(20) unsigned NOT NULL,
    `tag_id`  int(10) unsigned    NOT NULL,
    PRIMARY KEY (`file_id`, `tag_id`),
    CONSTRAINT `FK__files` FOREIGN KEY (`file_id`) REFERENCES `files` (`file_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

INSERT INTO `files_tags` (`file_id`, `tag_id`)
VALUES (100, 1),
       (100, 2),
       (100, 3),
       (101, 1),
       (101, 2);

SQL fiddle: http://www.sqlfiddle.com/#!9/b776ab/3

I have a join table (files_tags) which lists tags for another table (files). I have the following query, which works great to select all files tagged with tags 1 and 2:

SELECT f.filename
FROM files_tags ft0
         INNER JOIN files f ON ft0.file_id = f.file_id
         INNER JOIN files_tags ft1 ON f.file_id = ft1.file_id AND ft1.tag_id = 2
WHERE ft0.tag_id = 1;

I'm looking for a query that selects files tagged 1 AND 2 but NOT 3. I tried the above query with this additional join:

         INNER JOIN files_tags ft2 ON f.file_id = ft2.file_id AND ft1.tag_id = 3

but that just ends up giving me 5 rows back (identical to the files_tags table) which is not what I want. My expected output for this is a single row of file_101.ext. What would be the proper query to get files that are tagged 1 AND 2 but NOT 3?

Upvotes: 1

Views: 61

Answers (2)

STSchiff
STSchiff

Reputation: 36

Not sure if this is elegant enough, but should be quit performant.

SELECT f.filename
FROM files_tags ft0
     INNER JOIN files f ON ft0.file_id = f.file_id
     INNER JOIN files_tags ft1 ON f.file_id = ft1.file_id AND ft1.tag_id = 2
     LEFT JOIN files_tags ft2 on f.file_id = ft2.file_id and ft2.tag_id = 3
 WHERE ft0.tag_id = 1
 and ft2.file_id is null;

Upvotes: 2

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can use exists/ not exists to check the existence and absence of related records as

select  f.filename
from files f
where exists (
  select 1
  from files_tags
  where tag_id = 1
  and file_id = f.file_id
) and exists (
  select 1
  from files_tags
  where tag_id = 2
  and file_id = f.file_id
) and not exists (
  select 1
  from files_tags
  where tag_id = 3
  and file_id = f.file_id
)

demo

Upvotes: 1

Related Questions