Get MysQL rows where 2 words match

Question

I am trying to build an easy search based on 2 MySQL tables. One called keywords (words) and another called keyword2data (map which binds words to datasource).

Keywords holds id and keyword whilst keywords2data holds keyword_id and data_id.

data_id it self is a reference to a 3rd but in this case unimportant table.

What i want is to be able to search for example "dog sled" and get all data_id's which has those keywords bound to it.

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 data_id which has either dog or sled bound to it, not necessary both, which is 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

Gives me nothing since no single row in keywords2data holds 2 keywords.

What is the right way to do this?

Answer 1

How about something like

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