difficult MySQL issue (relations and multiple rows)

I have a difficult mysql-question. I have a database that matches keywords to cases, simplified:

TABLE cases (id)
TABLE keywords (id)
TABLE cases_keywords (case, keyword)

So I could have case A with keywords Y and Z and I could have case B with keywords X and Y. This gives me four rows in cases_keywords:

(A, Y)
(A, Z)
(B, X)
(B, Y)

The problem:

I have a search on my site where users type in keywords to search. I want the results to show matches when all keywords are found. So when user types in Y and Z as keywords only case A appears (as case B does not have keyword Z). But when the user only gives Y as keyword the site shows both A and B (as they both got keyword Y).

So I know the queryis dynamic via PHP, the big question is to match for multiple keywords... Let's say I want to match for 2 keywords: Y and Z with result: case A.

How do I program this into a query? How can I match on multiple rows?

SELECT C.id FROM cases C JOIN cases_keywords CK ON CK.case = C.id WHERE CK.keyword = Y AND CK.keyword = Z

Above is not working, so I tried something with WHERE C.case IN () but I got stuck there too...

Someone please help me

Answers


2 suggestions here.

first one must be built dynamically and won't be that efficient, but it is easy to understand.

SELECT C.id 
FROM cases C 
JOIN cases_keywords CK1 ON CK1.case = C.id AND CK1.keyword = Y
JOIN cases_keywords CK2 ON CK2.case = C.id AND CK2.keyword = Z

More efficient and can be built non dynamically:-

SELECT C.id, COUNT(CK.keyword) AS KeywordCount
FROM cases C 
JOIN cases_keywords CK ON CK.case = C.id
WHERE CK.keyword IN ('Y', 'Z')
GROUP BY C.id
HAVING KeywordCount = 2

If you can use MyISAM storage engine (or perhaps InnoDB in MySQL 5.6) you can do the following with fulltext indexes (which then allows you to have keywords that are actually phrases):

CREATE TABLE cases (id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(10) NOT NULL) Engine = MyISAM;

CREATE TABLE keywords (id INT AUTO_INCREMENT PRIMARY KEY,
                       word VARCHAR(10) NOT NULL) Engine = MyISAM;

ALTER TABLE keywords ADD FULLTEXT INDEX (word);

CREATE TABLE cases_keywords (`case` INT,
                             keyword INT,
                             PRIMARY KEY (`case`,keyword)) Engine = MyISAM;

INSERT INTO cases VALUES (1,'Alpha'),(2,'Beta');
INSERT INTO keywords VALUES (1,'Xylophone'),(2,'Yaks'),(3,'Zebra');

INSERT INTO cases_keywords VALUES (1,2),(1,3),(2,1),(2,2);

SELECT `cases`.name, COUNT(keywords.id) AS matches
FROM  `cases` 
JOIN cases_keywords
  ON cases_keywords.case = `cases`.id
JOIN keywords
  ON keywords.id = cases_keywords.keyword
WHERE MATCH(word) AGAINST ('(Yaks Zebra) ("Yaks Zebra") (+Yaks* +Zebra*)' IN BOOLEAN MODE)
GROUP BY `cases`.id
ORDER BY matches DESC

Working SQLFiddle: http://sqlfiddle.com/#!2/49c19/2

Obviously you can tweak the fulltext search to work how you want, but we used the following BOOLEAN MODE search to do the following (which fit our requirements):

(Yaks Zebra) -> matches any occurence in the `word` column of Yak or Zebra
("Yaks Zebra") -> matches the whole phrase "Yaks Zebra" in the `word` column
(+Yaks* +Zebra*) -> requires both Yaks and Zebra to be in the `word` column, but
                    also allows extra characters after each of those words...

Need Your Help

Error On MongoDB Start Up

mongodb macos startup osx-yosemite database

When I run the mongod command on my Mac terminal, I receive the following errors/traces:

I'm giving a presentation on social media with a short part based on Stack Overflow. How would you describe its key principles?

discussion meta presentations

I'm giving a presentation on social media with a short part based on Stack Overflow. How would you describe its key principles? What are its key benefits over the traditional forum/mailing list