CODEWITHSUNDEEP
mysqlsql
Nick Schroeder
Nick Schroeder

Reputation: 3

MYSQL: Select Query with multiple values from one column

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!

Upvotes: 0

Views: 1866

Answers (2)

forpas
forpas

Reputation: 164214

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.

Upvotes: 1

Dalton
Dalton

Reputation: 23

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.

Upvotes: 0

Related Questions