我正在尝试同时匹配userIngredient.i_id和recipe_ingredient.i_id,并比较并比较配方_ingredient.i_id的所有值,我尝试查询的结果仅显示所有匹配的i_id,而没有不匹配的i_id,这是数据-
recipe_ingredients表:
+---------+------+
| post_id | i_id |
+---------+------+
| ifqnnv | 1 |
+---------+------+
| ifqnnv | 2 |
+---------+------+
| ifqnnv | 3 |
+---------+------+
| ifqnnv | 4 |
+---------+------+
userIngredient表:
+---------+------+
| user_id | i_id |
+---------+------+
| 4 | 1 |
+---------+------+
| 4 | 2 |
+---------+------+
| 4 | 3 |
+---------+------+
我尝试过的查询:
SELECT userIngredients.i_id,recipe_ingredients.i_id,recipe_ingredients.recipe_id,CASE
WHEN userIngredients.i_id = recipe_ingredients.i_id THEN "true"
WHEN userIngredients.i_id != recipe_ingredients.i_id THEN "false"
END as state
FROM userIngredients
LEFT OUTER JOIN recipe_ingredients
ON userIngredients.i_id = recipe_ingredients.i_id
WHERE userIngredients.uid = 4 AND recipe_ingredients.post_id = 'ifqnnv'
我得到的输出:
+------+------+-----------+-------+
| i_id | i_id | recipe_id | state |
+------+------+-----------+-------+
| 1 | 1 | ifqnnv | true |
+------+------+-----------+-------+
| 2 | 2 | ifqnnv | true |
+------+------+-----------+-------+
| 3 | 3 | ifqnnv | true |
+------+------+-----------+-------+
所需的输出:
+------+------+-----------+-------+
| i_id | i_id | recipe_id | state |
+------+------+-----------+-------+
| 1 | 1 | ifqnnv | true |
+------+------+-----------+-------+
| 2 | 2 | ifqnnv | true |
+------+------+-----------+-------+
| 3 | 3 | ifqnnv | true |
+------+------+-----------+-------+
| null | 4 | ifqnnv | false |
+------+------+-----------+-------+