Finding all products with same keywords

I’ve got three tables:

products(product_id, product_description)
keywords(keyword_id, keyword_name)
product_keyword(FOREIGN KEY(product_id) REFERENCES products(product_id), FOREIGN KEY (keyword_id)  REFERENCES keywords(keyword_id))

If I want to return all products with the same keywords as a given product, how would I do this?

I’ve tried something along the lines of:

SELECT * from products
WHERE product_keyword having product_keyword.product_id = 1 /* don't know how to make this a general statement, but let's assume that I'm looking for all products with the same keywords as product number 1

Can’t figure it out.

Answer

One method is to concatenate the keywords together and use that string for joining:

select p.product_id, p.keyword_ids
from (select product_id, group_concat(keyword_id order by keyword_id) as keyword_ids
      from product_keywords
      group by product_id
     ) p join
     (select group_concat(keyword_id order by keyword_id) as keyword_ids
      from product_keywords
      where product_id = 1
     ) p1
     on p.keyword_ids = p1.keyword_ids;

Another method is more cumbersome but uses more traditional SQL. Do a self-join on keywords and a lot of counting:

select p.product_id
from key_words p left join
     key_words p1
     on p1.key_word = p.key_word nd
        p1.product_id = 1
group by p.product_id
having count(*) = count(p1.keyword) and
       count(*) = (select count(*) from key_words pp1 where pp1.product_id = 1);

The left join keeps all keywords for each product. The having then does two things:

• The count(*) = count(p1.keyword) returns only rows where all keywords match.
• The count(*) = <subquery> ensures that the number of keywords is the number for the first product.

Both of these formulations assume no duplicates in product_keywords, which seems like a reasonable assumption. Judicious use of distinct works if that is an issue.