Left Join query returns duplicates

left-join, mysql, sql

Solution

You are missing the condition in your `left join`:

SELECT a.id,
       a.name,
       sa.dose
FROM   additives a
LEFT JOIN stage_additives sa
ON     a.id = sa.additive_id and sa.stage_id = 195;

Remember, the `join` is conceptually doing a `cross join` between the two tables and taking only the rows that match the `on` condition (the `left join` is also keeping all the rows in the first table). By not having an `on` condition, the join is keeping all pairs of rows from the two tables where `sa.stage_id = 195` -- and that is a lot of pairs.

EDIT:

(In response to moving the condition `sa.stage_id = 195` into a `where` clause.)

The condition `sa.stage_id = 195` is in the `on` clause on purpose. This ensures that the `left join` actually behaves as written. If the condition were moved to a `where` clause, then the `left join` would turn into an `inner join`. Rows from `additive` with no match in `stage_additive` would have a `NULL` value for `sa.stage_id` and be filtered out. I have to assume that the OP intended for the `left join` to keep all rows in `additive` because of the explicit use of `left join`.

Problem

I have additives table: ``` id name 30 gro 31 micro 32 bloom 33 test ``` And stage_additives table: ``` stage_id additive_id dose 195 30 2 195 31 3 195 32 1 ``` Mysql query: ``` SELECT a.id, a.name, sa.dose FROM additives a LEFT JOIN stage_additives sa ON sa.stage_id = 195 ``` Result is: ``` id name dose 32 Bloom 2 32 Bloom 3 32 Bloom 1 30 Gro 2 30 Gro 3 30 Gro 1 31 Micro 2 31 Micro 3 31 Micro 1 33 test 2 33 test 3 33 test 1 ``` This does not make sense to me as there ore 3 of each item in the result even though there is only one item per each table with same id/name. I also tried inner join, right join but result is almost identical except for order. What I want is all id, name from additives and dose from stage_additives if it exists otherwise NULL (or better still custom value of 0)

Original source