mercredi 26 octobre 2016

MySQL SELECT runs multiple times

When I run my SELECT statement it runs the IFNULL part thrice and the SUM CASE part twice. I know it has something to do with too many LEFT JOIN in the statement. But unable to find a solution to the issue.

It is hard to explain the problem so I have included sqlfiddle link.

SQLFIDDLE

SELECT 
IFNULL(SUM(d.quantity),0) as dispatch, 
SUM(CASE WHEN t.valuefor='alpha' THEN t.quantity ELSE 0 END) as alpha,
SUM(CASE WHEN t.valuefor='beta' THEN t.quantity ELSE 0 END) as beta 
FROM conversion as c 
LEFT JOIN sale as s ON s.conversionid = c.id 
LEFT JOIN dispatch as d ON d.saleid = s.id 
LEFT JOIN test as t ON t.conversionid = c.id

Current Output

dispatch    alpha   beta
1800        1400    80

Desired Output

dispatch    alpha   beta
600         700     40

Aucun commentaire:

Enregistrer un commentaire