mysql sum join,MYSQL：JOIN使SUM加倍

This should be really easy. Believe me, I've been researching this for hours. My query:

SELECT not_piece.pid, part.name AS 'Part Name', SUM(qty_left) AS 'In Stock'

FROM not_piece

JOIN part ON not_piece.pid = part.pid

GROUP BY part.name;

Only two tables, not_piece and part.

select qty_left

from not_piece

where pid='M-MP-007r8';

returns 5.

Since the part.name appears twice in the parts table (that's fine), the sum is 10, not 5.

How do I make this join without doubling the sum?

Thanks.

解决方案SELECT t.pid, p.name AS 'Part Name', t.InStock AS 'In Stock'

FROM (SELECT pid, SUM(qty_left) AS InStock

FROM not_piece

GROUP BY pid) t

INNER JOIN part p

ON t.pid = p.pid