mySQL Left outer join not selecting possible all data

Question

Im trying to match both userIngredient.i_id and recipe_ingredient.i_id and match and compare all values of recipe_ingredient.i_id, the query I tried only displays all matching i_id's without the non matching i_id's, heres the data-

recipe_ingredients table:

+---------+------+
| post_id | i_id |
+---------+------+
| ifqnnv  | 1    |
+---------+------+
| ifqnnv  | 2    |
+---------+------+
| ifqnnv  | 3    |
+---------+------+
| ifqnnv  | 4    |
+---------+------+

userIngredient table:

+---------+------+
| user_id | i_id |
+---------+------+
| 4       | 1    |
+---------+------+
| 4       | 2    |
+---------+------+
| 4       | 3    |
+---------+------+

Query that I've tried:

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'

Output I got:

+------+------+-----------+-------+
| i_id | i_id | recipe_id | state |
+------+------+-----------+-------+
| 1    | 1    | ifqnnv    | true  |
+------+------+-----------+-------+
| 2    | 2    | ifqnnv    | true  |
+------+------+-----------+-------+
| 3    | 3    | ifqnnv    | true  |
+------+------+-----------+-------+

Desired output:

+------+------+-----------+-------+
| i_id | i_id | recipe_id | state |
+------+------+-----------+-------+
| 1    | 1    | ifqnnv    | true  |
+------+------+-----------+-------+
| 2    | 2    | ifqnnv    | true  |
+------+------+-----------+-------+
| 3    | 3    | ifqnnv    | true  |
+------+------+-----------+-------+
| null | 4    | ifqnnv    | false |
+------+------+-----------+-------+

GMB · Answer 1 · 2019-11-04T21:41:10.513

Move the condition on the left joined table to the on side of the join. Otherwise, this condition can never be fulfilled when the left join does not match, and the corresponding record is eliminated from the resultset.

SELECT 
    i.i_id,
    r.i_id, 
    r.recipe_id,
    CASE
        WHEN i.i_id = r.i_id THEN 'true'
        ELSE 'false'
    END as state
FROM 
    userIngredients u
    LEFT OUTER JOIN recipe_ingredients r
        ON i.i_id = r.i_id
        AND r.post_id = 'ifqnnv'
WHERE i.uid = 4

Side notes:

meaningfull table aliases make the query more concise and easier to understand; use them at all times when more than one table is involved in the query
the case expression can be simplified to WHEN ... ELSE ..., since both conditions being check are logically opposed
use single quotes instead of double quotes to delimit strings; this corresponds to the SQL standard, while also some RDBMS use double quotes for identifiers
mixing table names with camel case (userIngredient) and underscore separated (recipe_ingredients) is error prone; matter of fact, use underscore separated table and column names, since some RDBMS manage table names in a case-insenstive manner, making camel case pointless

score 0 · Answer 2 · answered Nov 04 '19 at 21:38

0

When using left join, order matters. To get your desired result join userIngredients on recipe_ingredients

answered Nov 04 '19 at 21:38

MCI

888
1
7
13

mySQL Left outer join not selecting possible all data

2 Answers2