如何在多对多表中为所有值组合select语句

Question

I'm working on an existing product database trying to clean things up. I have the following tables:

Tables

<products> id, title
<keywords> id, title
<product_has_keyword> product_id, keyword_id

When I want to generate a list of products I use the code:

SELECT * FROM products;

And then for each product:

SELECT k.title FROM keywords k, product_has_keyword phk WHERE k.id = phk.keyword_id AND phk.id = ?

How would I change this code into a single SELECT that perhaps returns products in addition to the keywords (separated by spaces) like "id, title, keywords"?

Such as (1, "hammer", "tool home hand"), (2, "blender", "kitchen home"), etc...

Answer 1

You can use the GROUP_CONCAT but be aware of that it has the limit of character to group

SELECT p.id, p.title ,GROUP_CONCAT(k.title SEPARATOR ' ') `keywords`
FROM 
products p
LEFT JOIN product_has_keyword phk ON(p.id = phk.product_id)
LEFT JOIN keywords k ON (k.id = phk.keyword_id )
WHERE  phk.id = ?
GROUP BY p.id