MySQL: Select distinct values from one column if other column has two determined values in junction table

Question

I have junction table:

CREATE TABLE `book_tags` (
  `id` int(11) NOT NULL,
  `book_id` int(11) DEFAULT NULL,
  `tag_id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

with values like

INSERT INTO `book_tags` (`id`, `book_id`, `tag_id`) VALUES
(3, 20, 1),
(4, 20, 2),
(5, 21, 1),
(6, 22, 2),
(7, 24, 2);

How can I find the books (book_id) which have two or more determined tags at the same time? For example, how can I find a book which have tag_id=2 AND tag_id=1

+++++++++++++++++++++++++++++++++++++++

UPDATED:

Looking through stackoverflow I've found the answer on my question.

For 2 required tags the solution will be:

SELECT * FROM `book_tags`
WHERE `tag_id` IN (1, 2)
GROUP BY book_id 
HAVING COUNT(*) = 2

This solution is suitable for my particular case, since I know that in my table there is no rows with the same pair of values of book_id and tag_id.

Thank you @Barbaros Özhan and @scaisEdge for help!

score 2 · Accepted Answer · edited Aug 05 '19 at 07:47

2

You could try using a subquery for tag_id in you searched set and the check for count distinct tag_id quae to the number of your searched tag

Assuming tag 1, 2 then

select book_id 
from (
  select book_id, tag_id 
  from  book_tags 
  where tag_id in  (1,2)
) t 
group by book_id
having count(distinct tag_id) = 2  


select book_id 
from (
  select book_id, tag_id 
  from  book_tags 
  where tag_id in  (1,2,5,7, 9, 11)
) t 
group by book_id
having count(distinct tag_id) = 5

edited Aug 05 '19 at 07:47

marc_s

732,580
175
1,330
1,459

answered Jul 30 '19 at 12:52

ScaisEdge

131,976
10
91
107

Thank you @scaisEdge! It's a good idea! But If there will be 5 or more required tags, which book should have? – oshka Jul 30 '19 at 13:30
@oshka the query conceptually remains the same .. just change the IN clause with the proper value and the Having constraints with the number of tag required .. (in the sample you provided 5 tags return no books) – ScaisEdge Jul 30 '19 at 13:49
@oshka Note that we're still using IN() here – Strawberry Jul 30 '19 at 13:51
1

anyway answer updated with a sample for 5 tag with id (1,2,5,7, 9, 11) – ScaisEdge Jul 30 '19 at 13:52

Barbaros Özhan · Answer 2 · 2019-07-30T13:59:47.877

1

It would be an elastic option using a having clause as in the following way as you add after in operator's list(in (1,2,3,...)) more tag_id to filter:

select `book_id` 
  from `book_tags` 
 where `tag_id` in (1,2,3) 
 group by `book_id` 
having count(`tag_id`) = count(distinct `tag_id`)
   and count(distinct `tag_id`) > count(distinct `book_id`);

Demo

edited Jul 30 '19 at 13:59

answered Jul 30 '19 at 13:52

Barbaros Özhan

59,113
10
31
55

M. Suleiman · Answer 3 · 2019-07-30T12:54:55.340

0

SELECT book_id FROM books_tags WHERE tag_id = 1 OR tag_id = 2?

Or

SELECT book_id FROM books_tags WHERE tag_id IN (1, 2)

edited Jul 30 '19 at 12:54

answered Jul 30 '19 at 12:49

M. Suleiman

858
4
22

Thank you @Mido! Bot I need to find all book which have both tag_id=2 AND tag_id=1 – oshka Jul 30 '19 at 13:28
@oshka the above query would find the book_ids 3, 4, 5, 6, 7 as each one of those has tag_id 1 or tag_id 2. You might be confusing my usage of _and_ and _or_. Saying that tag_id = 1 OR 2 would mean "get all the values that have tag_id = 1 and also get all the values that have tag_id = 2". Isn't this what you're trying to accomplish? – M. Suleiman Jul 30 '19 at 13:49

MySQL: Select distinct values from one column if other column has two determined values in junction table

3 Answers3