PHP mysql 在两个表中搜索一个值然后将其合并

Question

我有一个问题，当搜索两个表并使用关系表显示来显示时，如何管理显示。我给一个样本

table "plates"

ID | NAME
----------------
1  | piza
2  | soft drink
3  | ice cream

table "ingredients"

ID | NAME 
-------------------
1  | tomato 
2  | pepperoni
3  | ice 
4  | mint leaf 
5  | lemon 
6  | ice manggo
7  | ice watermelon 

table "plate_ingredient_relations"

ID | PLATE_ID | PLATE_INGREDIENTS 
-----------------------------------
1  |     1    |     1
2  |     1    |     2
3  |     2    |     3
4  |     2    |     4
5  |     2    |     5
6  |     3    |     6
7  |     3    |     7

这里是我的查询，如果我想搜索“ice”

SELECT 'plate' AS type, id, name FROM `plates` WHERE name like '%ice%'
UNION
SELECT 'ingredient', id, name FROM `ingredients` WHERE name like '%ice%'

结果是这样的

type        | id | name 
---------------------------------
plate       |  3 | ice cream
ingredient  |  3 | ice
ingredient  |  6 | ice manggo
ingredient  |  7 | ice watermelon

问题是。从那个结果来看。我可以像这样显示结果吗？

soft drink - ice 
ice cream  - ice manggo 
ice cream  - ice watermelon

是否与表“plate_ingredient_relations”相关

Answer 1

SELECT CONCAT(p.name, " - ", i.name) as plate_ingredient
FROM

plate_ingredient_relations r
    JOIN plates p
        ON r.plate_id = p.id

    JOIN ingredients i
        ON r.plate_ingredients = i.id

WHERE
    p.name like '%ice%'
    OR i.name like '%ice%';