1

I have 3 tables as shown in the link sqlfiddle When I take the sum with two tables I get good result sqlfiddle

but when I add 3rd table and it's query I get unexpected result as this

http://sqlfiddle.com/#!2/f9858a/12

How can I resolve this problem pls ?

2
  • can you explain a little bit what is unexpected?
    – Riad
    Dec 10, 2014 at 17:58
  • The sums are changing as shown in two sqlfiddle. For exemple quantity of Beurre which must 500 becomes 1000 in second query all the sums are doubled Dec 10, 2014 at 18:12

2 Answers 2

1

When you do the aggregation without ingredient, then LEFT JOIN result to ingredient you get this

mysql> SELECT A.*,ig.qty FROM
    -> (SELECT dm.id,dm.nom,
    -> SUM(IF(dm.id=sd.matid, quantite,0)) as thisqty,
    -> SUM(IF(dm.id=sd.matid, sd.montant * (1+sd.tvaval/100),0)) AS thissum
    -> FROM stockdata AS sd
    -> JOIN defmatiere as dm ON dm.id=sd.matid
    -> WHERE dm.type=1 GROUP BY dm.nom ORDER BY dm.id) A
    -> LEFT JOIN ingredient as ig ON ig.matid=A.id;
+----+----------+---------+--------------------+------+
| id | nom      | thisqty | thissum            | qty  |
+----+----------+---------+--------------------+------+
|  1 | Amande   |   19470 | 204.74464999999998 |  600 |
|  1 | Amande   |   19470 | 204.74464999999998 |  200 |
|  2 | Chocolat |    7920 |              62.97 |  100 |
|  2 | Chocolat |    7920 |              62.97 |  200 |
|  4 | Beurre   |     500 |               2.48 |   60 |
|  4 | Beurre   |     500 |               2.48 |   60 |
+----+----------+---------+--------------------+------+
6 rows in set (0.00 sec)

mysql>

Perhaps doing an aggregation after the INNER JOIN would help

mysql> SELECT A.*,SUM(ig.qty) igqty FROM
    -> (SELECT dm.id,dm.nom,
    -> SUM(IF(dm.id=sd.matid, quantite,0)) as thisqty,
    -> SUM(IF(dm.id=sd.matid, sd.montant * (1+sd.tvaval/100),0)) AS thissum
    -> FROM stockdata AS sd
    -> JOIN defmatiere as dm ON dm.id=sd.matid
    -> WHERE dm.type=1 GROUP BY dm.nom ORDER BY dm.id) A
    -> LEFT JOIN ingredient as ig ON ig.matid=A.id
    -> GROUP BY A.id,A.nom,A.thisqty,A.thissum;
+----+----------+---------+--------------------+-------+
| id | nom      | thisqty | thissum            | igqty |
+----+----------+---------+--------------------+-------+
|  1 | Amande   |   19470 | 204.74464999999998 |   800 |
|  2 | Chocolat |    7920 |              62.97 |   300 |
|  4 | Beurre   |     500 |               2.48 |   120 |
+----+----------+---------+--------------------+-------+
3 rows in set (0.00 sec)

mysql>
9
  • Thanks I am sorry to not to vote because my reputation is insufficient. I should ask this in stackoverflow. I'll keep in mind Dec 10, 2014 at 19:16
  • You should be able to upvote now. Your rep is 23. Dec 10, 2014 at 19:27
  • If this answered your question, you can mark it acepted by clicking on the checkmark on my question. Dec 10, 2014 at 21:04
  • This was exact answer which resolved my problem. However I forgot to add date filter by sd.st_date which exists in sqlfiddle. I did not find where to put this filter any suggestion ? It's like WHERE "sd.st_date>=" +1stdate + " AND sd.st_date<=" + 2nddate. Dec 10, 2014 at 21:38
  • Between WHERE dm.type=1 and GROUP BY dm.nom ORDER BY dm.id Dec 10, 2014 at 21:43
0

However my ingredient table (can be seen in the slqfiddle) was not suitable for this query. Finally I added a date column in ingredient table so i can retreive with date condition as expected.

The result is in the link

sqlfiddle

Any idea without addition of date column will be appreciated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.