MYSQL: Select Query with multiple values from one column

Question

i am currently working with a MYSQL-Database whichhas three tables: Books, Keywords and KeywordAssignment.

The tables Books and Keywords are in a many to many relationship therefore the table KeywordAssignment.

Database structure

Now to my question: I want to search for a book with multiple (max: up to ten) keywords.

I've already tried a self join:

SELECT BookID 
FROM Keywords K1 INNER JOIN
     Keywords K2
     ON K1.KeywordAssignmentID=K2.KeywordAssignmentID INNER JOIN 
     KeywordAssignment
     ON KeywordAssignment.KeywordAssignmentID=K1.KeywordAssignmentID INNER JOIN
     Books
     ON KeywordAssignment.BookID=Books.BookID
WHERE K1.Keyword='Magic' AND K2.Keyword='Fantasy'

The problem is it only works if the given Keyword are in the right order. If they aren't there are more than one.

I appreciate your help thank you very much!

forpas · Accepted Answer · 2019-07-24 21:20:56Z

1

You need to GROUP BY BookID and a HAVING clause with the condition that both keywords are linked to that BookID:

SELECT b.BookID, b.Title 
FROM Books b
INNER JOIN KeywordAssignment ka ON ka.BookID = b.BookID
INNER JOIN Keyword k ON k.KeywordID = ka.KeywordID
WHERE k.Keyword IN ('Magic', 'Fantasy')
GROUP BY b.BookID, b.Title
HAVING COUNT(DISTINCT k.Keyword) = 2

This code will return books that are linked to both 'Magic' and 'Fantasy'.
If you want either of the 2 keywords then remove the HAVING clause.

edited Jul 24, 2019 at 21:20

answered Jul 24, 2019 at 21:15

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nick Schroeder Over a year ago

Thank you very much for your quick answer. That was the solution i was looking for. It worked!

Dalton · Accepted Answer · 2019-07-24 21:18:30Z

0

If I understand your question correctly, you want to query for books that have multiple key words. The key word there is have. I don't have MYSQL but the query should look something like this:

SELECT B.BookID, COUNT(*) as NumberOfKeywords FROM Books B
INNER JOIN KeywordAssignment KA
ON B.BookID = KA.BookID
INNER JOIN Keywords K
ON KA.KeywordID = K.KeywordID
GROUP BY B.BookID
HAVING NumberOfKeywords > 0 AND NumberOfKeywords <= 10

What we are doing is grouping by each book and then selecting the ones that have more than 0 keywords and less than 10.

answered Jul 24, 2019 at 21:18

Dalton

234 bronze badges

Collectives™ on Stack Overflow

MYSQL: Select Query with multiple values from one column

Now to my question: I want to search for a book with multiple (max: up to ten) keywords.

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Now to my question: I want to search for a book with multiple (max: up to ten) keywords.

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related