CODEWITHSUNDEEP
mysqlsearch
JamieHoward
JamieHoward

Reputation: 693

MySQL Query: Filtering search results by tags

Suppose that you have a database table that works as a link between two others. In this case, a link between a book and a tag, such as "fiction" or "gardening". If we have the following dataset:

Book_id | tag_id
----------------
1       | 13
1       | 43
1       | 15
2       | 13
2       | 25

What kind of query would you run to say "Find only the books that have links with tags 13, 43, AND 15"? That is, the more tags you add, the smaller the number of books shown.

I hope that makes sense. Thanks in advance for your time and help!!

Upvotes: 0

Views: 176

Answers (3)

Akash
Akash

Reputation: 5012

Try this

SELECT
  Book_id
FROM
  book_tags
GROUP BY
  Book_id
HAVING 
  SUM( CASE WHEN tag_id IN (13, 43, 15) THEN 1 END ) >= 3

You only need the books that have tags 13, 43, 15 (all of them), it returns book_id = 1 for your result. SUM() >= 3 specifies the total number of tags you are searching, in this case its 3, i.e 13, 43, 15

SQLFIDDLE

Upvotes: 1

m79lkm
m79lkm

Reputation: 3070

you can try something like this:

select book_table.* from book_table, link_table where book_table.id = link_table.book_id and link_table.tag_id in (13, 43, 15)

If you have a separate table for your books..

Upvotes: 0

faq
faq

Reputation: 3076

If i understand your question correctly... Like this:

SELECT `Book_id` FROM `table` WHERE `tag_id` IN (13,43,25) LIMIT 0, 10;

Upvotes: 0

Related Questions