CODEWITHSUNDEEP
mysqlsqlleft-join
DominicM
DominicM

Reputation: 6888

Left Join query returns duplicates

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)

Upvotes: 5

Views: 46213

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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.

Upvotes: 12

xanatos
xanatos

Reputation: 111820

You probably wanted something like

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

SQLFiddle: http://sqlfiddle.com/#!2/fd607/4

In this case, instead of a LEFT JOIN you can use an INNER JOIN, because the WHERE clause is based on the success of the join.

SELECT a.id,
       a.name,
       sa.dose
FROM   additives a
INNER JOIN stage_additives sa 
           ON sa.additive_id = a.id AND sa.stage_id = 195

SQLFiddle: http://sqlfiddle.com/#!2/fd607/8

But perhaps you really wanted a LEFT JOIN:

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

This returns a fourth row:

33  test    (null)

SQLFiddle: http://sqlfiddle.com/#!2/fd607/7

Upvotes: 0

kwarunek
kwarunek

Reputation: 12587

in clause ON should be relation between tables and condition for stage_id in WHERE

Upvotes: 1

Related Questions