Get MysQL strings where there are 2 words

Question

I am trying to create a simple search based on tables 2 MySQL. One of the named keywords (words) and the other is called keyword2data (a map that associates words with a data source).

Keywords contain id and keyword whilst keywords2data contains keyword_id and data_id.

data_id is a link to the 3rd, but in this case non-essential table.

I want to be able to search, for example, "dog sled", and get all the data_id that are associated with these keywords.

SELECT k2d.`data_id` , k2d.`keyword_id` 
FROM keywords2data as k2d, keywords as k 
WHERE k2d.`keyword_id` = k.`id` 
&& (k.`keyword` = 'dog' || k.`keyword` = 'sled') 
LIMIT 10

Gives me all the data_id that has a dog or sled attached to it, not necessarily both what I want.

SELECT k2d.`data_id` , k2d.`keyword_id` 
FROM keywords2data as k2d, keywords as k 
WHERE k2d.`keyword_id` = k.`id` 
&& (k.`keyword` = 'dog' && k.`keyword` = 'sled') 
LIMIT 10

It does not give me anything, since not a single line in keywords2data contains 2 keywords.

What is the right way to do this?

Answer 1

?

SELECT k2d.`data_id`, 
       k2d.`keyword_id` 
FROM   keywords2data AS k2d 
       INNER JOIN keywords AS k 
         ON k2d.`keyword_id` = k.`id` 
WHERE  k.`keyword` IN ( 'dog', 'sled', 'rex' ) 
GROUP  BY k.keyword 
HAVING COUNT(*) = 3 

Answer 2

-

SELECT  k2d.`data_id` , 
        k2d.`keyword_id`  
FROM    keywords2data as k2d INNER JOIN
        keywords as k  ON k2d.`keyword_id` = k.`id` INNER JOIN
        keywords as k2  ON k2d.`keyword_id` = k2.`id`
WHERE   k.`keyword` = 'dog' 
AND     k2.`keyword` = 'sled'
LIMIT 10 

Answer 3

Perhaps it?

To increase the number of keywords, you simply add more words to the statement ORin the subquery and then change =2.

It is assumed that each data item is associated with a keyword, using kerywords2dataonce and only once.

SELECT k2d.data_id
     , k2d.keyword_id

FROM keywords2data AS k2d
   , keywords AS k 

WHERE k2d.keyword_id = k.id
  AND (
       SELECT COUNT(*)
        FROM keywords2data AS sqk2d
           , keywords AS sqk 
        WHERE sqk2d.data_id = k2d.data_id
          AND sqk2d.keyword_id = sqk.id
          AND (sqk.keyword = 'dog' || sqk.keyword = 'sled')
       ) = 2

LIMIT 10

Here is a version that does not return duplicate data_id(as in the comments), but also does not return any keywords:

SELECT k2d.data_id

FROM keywords2data AS k2d

WHERE (
       SELECT COUNT(*)
         FROM keywords2data AS sqk2d
            , keywords AS sqk 
        WHERE sqk2d.data_id = k2d.data_id
          AND sqk2d.keyword_id = sqk.id
          AND (sqk.keyword = 'dog' || sqk.keyword = 'sled')
      ) = 2

LIMIT 10