# Left join problem

**URL:** <https://www.phphelp.com/t/left-join-problem/32318>\
**Category:** MySQL database\
**Created:** [August 5, 2020, 3:38am UTC](https://www.phphelp.com/t/left-join-problem/32318 "2020-08-05T03:38:11Z")\
**Posts on this page:** 10\
**Page:** 2

<div class="post-metadata">

**Author:** ![ErnieAlex](https://www.phphelp.com/letter_avatar_proxy/v4/letter/e/bc8723/32.png) [@ErnieAlex](https://www.phphelp.com/u/ErnieAlex)\
**Post date:** [October 15, 2020, 3:24pm UTC](https://www.phphelp.com/t/left-join-problem/32318/21 "2020-10-15T15:24:14Z")

</div>

Had some free time. I entered the data you sent to me. I tested the code that I posted above. I made one minor change to it. I used standard QUOTES on the WHERE line. ‘COMPLETED’ section. I also used standard QUOTES on the test data you sent to me to install the data in my local MySQL database system.

This gave me the following info from the query above:

| [SUM(W.WASTAGE\_QUANTITY)](http://localhost/phpmyadmin/sql.php?db=test&table=PMT_PRODUCTION_STATISTICS&sql_query=SELECT+%0D%0A++++++++SUM%28W.WASTAGE_QUANTITY%29%2C%0D%0A++++++++SUM%28D.DURATION_DOWNTIME%29%2C%0D%0A++++++++P.PRODUCTION_DATE%2C+%0D%0A++++++++P.PRODUCT_LINE%2C+%0D%0A++++++++SUM%28P.DURATION%29%2C+%0D%0A++++++++SUM%28P.QUANTITY%29%2C+%0D%0A++++++++SUM%28P.NOMINAL_PRODUCTS%29%0D%0A%0D%0A++++FROM+PMT_PRODUCTION_STATISTICS+AS+P+%0D%0A++++++++LEFT+JOIN+PMT_DOWNTIME+AS+D+%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+D.LINE+AND+P.PRODUCTION_DATE+%3D+D.DATE_DOWNTIME+%0D%0A++++++++LEFT+JOIN+PMT_WASTE+AS+W%0D%0A++++++++++++ON+W.LINE_W+%3D+D.LINE+AND+W.DATE_WASTAGE+%3D+D.DATE_DOWNTIME+%0D%0AWHERE+P.APP_STATUS+%3D+%27COMPLETED%27+%0D%0AGROUP+BY+P.PRODUCT_LINE++&session_max_rows=25&is_browse_distinct=0) | [SUM(D.DURATION\_DOWNTIME)](http://localhost/phpmyadmin/sql.php?db=test&table=PMT_PRODUCTION_STATISTICS&sql_query=SELECT+%0D%0A++++++++SUM%28W.WASTAGE_QUANTITY%29%2C%0D%0A++++++++SUM%28D.DURATION_DOWNTIME%29%2C%0D%0A++++++++P.PRODUCTION_DATE%2C+%0D%0A++++++++P.PRODUCT_LINE%2C+%0D%0A++++++++SUM%28P.DURATION%29%2C+%0D%0A++++++++SUM%28P.QUANTITY%29%2C+%0D%0A++++++++SUM%28P.NOMINAL_PRODUCTS%29%0D%0A%0D%0A++++FROM+PMT_PRODUCTION_STATISTICS+AS+P+%0D%0A++++++++LEFT+JOIN+PMT_DOWNTIME+AS+D+%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+D.LINE+AND+P.PRODUCTION_DATE+%3D+D.DATE_DOWNTIME+%0D%0A++++++++LEFT+JOIN+PMT_WASTE+AS+W%0D%0A++++++++++++ON+W.LINE_W+%3D+D.LINE+AND+W.DATE_WASTAGE+%3D+D.DATE_DOWNTIME+%0D%0AWHERE+P.APP_STATUS+%3D+%27COMPLETED%27+%0D%0AGROUP+BY+P.PRODUCT_LINE++&session_max_rows=25&is_browse_distinct=0) | [PRODUCTION\_DATE](http://localhost/phpmyadmin/sql.php?db=test&table=PMT_PRODUCTION_STATISTICS&sql_query=SELECT+%0D%0A++++++++SUM%28W.WASTAGE_QUANTITY%29%2C%0D%0A++++++++SUM%28D.DURATION_DOWNTIME%29%2C%0D%0A++++++++P.PRODUCTION_DATE%2C+%0D%0A++++++++P.PRODUCT_LINE%2C+%0D%0A++++++++SUM%28P.DURATION%29%2C+%0D%0A++++++++SUM%28P.QUANTITY%29%2C+%0D%0A++++++++SUM%28P.NOMINAL_PRODUCTS%29%0D%0A%0D%0A++++FROM+PMT_PRODUCTION_STATISTICS+AS+P+%0D%0A++++++++LEFT+JOIN+PMT_DOWNTIME+AS+D+%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+D.LINE+AND+P.PRODUCTION_DATE+%3D+D.DATE_DOWNTIME+%0D%0A++++++++LEFT+JOIN+PMT_WASTE+AS+W%0D%0A++++++++++++ON+W.LINE_W+%3D+D.LINE+AND+W.DATE_WASTAGE+%3D+D.DATE_DOWNTIME+%0D%0AWHERE+P.APP_STATUS+%3D+%27COMPLETED%27+%0D%0AGROUP+BY+P.PRODUCT_LINE++%0AORDER+BY+%60P%60.%60PRODUCTION_DATE%60+DESC&session_max_rows=25&is_browse_distinct=0) | [PRODUCT\_LINE](http://localhost/phpmyadmin/sql.php?db=test&table=PMT_PRODUCTION_STATISTICS&sql_query=SELECT+%0D%0A++++++++SUM%28W.WASTAGE_QUANTITY%29%2C%0D%0A++++++++SUM%28D.DURATION_DOWNTIME%29%2C%0D%0A++++++++P.PRODUCTION_DATE%2C+%0D%0A++++++++P.PRODUCT_LINE%2C+%0D%0A++++++++SUM%28P.DURATION%29%2C+%0D%0A++++++++SUM%28P.QUANTITY%29%2C+%0D%0A++++++++SUM%28P.NOMINAL_PRODUCTS%29%0D%0A%0D%0A++++FROM+PMT_PRODUCTION_STATISTICS+AS+P+%0D%0A++++++++LEFT+JOIN+PMT_DOWNTIME+AS+D+%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+D.LINE+AND+P.PRODUCTION_DATE+%3D+D.DATE_DOWNTIME+%0D%0A++++++++LEFT+JOIN+PMT_WASTE+AS+W%0D%0A++++++++++++ON+W.LINE_W+%3D+D.LINE+AND+W.DATE_WASTAGE+%3D+D.DATE_DOWNTIME+%0D%0AWHERE+P.APP_STATUS+%3D+%27COMPLETED%27+%0D%0AGROUP+BY+P.PRODUCT_LINE++%0AORDER+BY+%60P%60.%60PRODUCT_LINE%60+ASC&session_max_rows=25&is_browse_distinct=0) | [SUM(P.DURATION)](http://localhost/phpmyadmin/sql.php?db=test&table=PMT_PRODUCTION_STATISTICS&sql_query=SELECT+%0D%0A++++++++SUM%28W.WASTAGE_QUANTITY%29%2C%0D%0A++++++++SUM%28D.DURATION_DOWNTIME%29%2C%0D%0A++++++++P.PRODUCTION_DATE%2C+%0D%0A++++++++P.PRODUCT_LINE%2C+%0D%0A++++++++SUM%28P.DURATION%29%2C+%0D%0A++++++++SUM%28P.QUANTITY%29%2C+%0D%0A++++++++SUM%28P.NOMINAL_PRODUCTS%29%0D%0A%0D%0A++++FROM+PMT_PRODUCTION_STATISTICS+AS+P+%0D%0A++++++++LEFT+JOIN+PMT_DOWNTIME+AS+D+%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+D.LINE+AND+P.PRODUCTION_DATE+%3D+D.DATE_DOWNTIME+%0D%0A++++++++LEFT+JOIN+PMT_WASTE+AS+W%0D%0A++++++++++++ON+W.LINE_W+%3D+D.LINE+AND+W.DATE_WASTAGE+%3D+D.DATE_DOWNTIME+%0D%0AWHERE+P.APP_STATUS+%3D+%27COMPLETED%27+%0D%0AGROUP+BY+P.PRODUCT_LINE++&session_max_rows=25&is_browse_distinct=0) | [SUM(P.QUANTITY)](http://localhost/phpmyadmin/sql.php?db=test&table=PMT_PRODUCTION_STATISTICS&sql_query=SELECT+%0D%0A++++++++SUM%28W.WASTAGE_QUANTITY%29%2C%0D%0A++++++++SUM%28D.DURATION_DOWNTIME%29%2C%0D%0A++++++++P.PRODUCTION_DATE%2C+%0D%0A++++++++P.PRODUCT_LINE%2C+%0D%0A++++++++SUM%28P.DURATION%29%2C+%0D%0A++++++++SUM%28P.QUANTITY%29%2C+%0D%0A++++++++SUM%28P.NOMINAL_PRODUCTS%29%0D%0A%0D%0A++++FROM+PMT_PRODUCTION_STATISTICS+AS+P+%0D%0A++++++++LEFT+JOIN+PMT_DOWNTIME+AS+D+%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+D.LINE+AND+P.PRODUCTION_DATE+%3D+D.DATE_DOWNTIME+%0D%0A++++++++LEFT+JOIN+PMT_WASTE+AS+W%0D%0A++++++++++++ON+W.LINE_W+%3D+D.LINE+AND+W.DATE_WASTAGE+%3D+D.DATE_DOWNTIME+%0D%0AWHERE+P.APP_STATUS+%3D+%27COMPLETED%27+%0D%0AGROUP+BY+P.PRODUCT_LINE++&session_max_rows=25&is_browse_distinct=0) | [SUM(P.NOMINAL\_PRODUCTS)](http://localhost/phpmyadmin/sql.php?db=test&table=PMT_PRODUCTION_STATISTICS&sql_query=SELECT+%0D%0A++++++++SUM%28W.WASTAGE_QUANTITY%29%2C%0D%0A++++++++SUM%28D.DURATION_DOWNTIME%29%2C%0D%0A++++++++P.PRODUCTION_DATE%2C+%0D%0A++++++++P.PRODUCT_LINE%2C+%0D%0A++++++++SUM%28P.DURATION%29%2C+%0D%0A++++++++SUM%28P.QUANTITY%29%2C+%0D%0A++++++++SUM%28P.NOMINAL_PRODUCTS%29%0D%0A%0D%0A++++FROM+PMT_PRODUCTION_STATISTICS+AS+P+%0D%0A++++++++LEFT+JOIN+PMT_DOWNTIME+AS+D+%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+D.LINE+AND+P.PRODUCTION_DATE+%3D+D.DATE_DOWNTIME+%0D%0A++++++++LEFT+JOIN+PMT_WASTE+AS+W%0D%0A++++++++++++ON+W.LINE_W+%3D+D.LINE+AND+W.DATE_WASTAGE+%3D+D.DATE_DOWNTIME+%0D%0AWHERE+P.APP_STATUS+%3D+%27COMPLETED%27+%0D%0AGROUP+BY+P.PRODUCT_LINE++&session_max_rows=25&is_browse_distinct=0) | |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 108 | 130 | 1399-07-14 00:00:00 | سراک | 3940 | 968000 | 606000 | |
| 10 | 10 | 1399-07-05 00:00:00 | نیک رز1 | 690 | 150000 | 207000 | |

I do not see any duplications in the output. Therefore, I can not duplicate any problems you describe.  
Did you try the code I posted?

---

<div class="post-metadata">

**Author:** ![ErnieAlex](https://www.phphelp.com/letter_avatar_proxy/v4/letter/e/bc8723/32.png) [@ErnieAlex](https://www.phphelp.com/u/ErnieAlex)\
**Post date:** [October 15, 2020, 9:52pm UTC](https://www.phphelp.com/t/left-join-problem/32318/23 "2020-10-15T21:52:34Z")

</div>

So, does that mean you solved it? Hope so!

---

<div class="post-metadata">

**Author:** ![mohamad](https://www.phphelp.com/letter_avatar_proxy/v4/letter/m/ba9def/32.png) [@mohamad](https://www.phphelp.com/u/mohamad)\
**Post date:** [October 16, 2020, 9:14am UTC](https://www.phphelp.com/t/left-join-problem/32318/24 "2020-10-16T09:14:00Z")

</div>

Thank you dear Ernie,  
No, if you pay attention to the results, it returns duplicate values.  
For example, sum(w.wastage\_quantity) for the first row of the result must be 54 (not 108). Also the other values are wrong. ( Those are duplicate or sometimes are 4 times).  
I don’t know why does it happen?  
Thanks for your reply,  
Very best regards

---

<div class="post-metadata">

**Author:** ![ErnieAlex](https://www.phphelp.com/letter_avatar_proxy/v4/letter/e/bc8723/32.png) [@ErnieAlex](https://www.phphelp.com/u/ErnieAlex)\
**Post date:** [October 16, 2020, 3:45pm UTC](https://www.phphelp.com/t/left-join-problem/32318/25 "2020-10-16T15:45:59Z")

</div>

You combine three tables using the product line as a line between them. Then, you collect sums of the data. But, you are combining the third table based on the second table, not the first table. A bit confused on the logic you set up for this part. Normally, you want everything based on the first table. Please try this query:

> ```
> SELECT 
> SUM(W.WASTAGE_QUANTITY),
> SUM(D.DURATION_DOWNTIME),
> P.PRODUCTION_DATE, 
> P.PRODUCT_LINE, 
> SUM(P.DURATION), 
> SUM(P.QUANTITY), 
> SUM(P.NOMINAL_PRODUCTS)
> 
> FROM PMT_PRODUCTION_STATISTICS AS P 
> LEFT JOIN PMT_DOWNTIME AS D 
> ON P.PRODUCT_LINE = D.LINE AND P.PRODUCTION_DATE = D.DATE_DOWNTIME 
> LEFT JOIN PMT_WASTE AS W
> ON P.PRODUCT_LINE = W.LINE_W AND P.PRODUCTION_DATE = W.DATE_WASTAGE
> WHERE P.APP_STATUS = 'COMPLETED' 
> GROUP BY P.PRODUCT_LINE
> 
> ```

There are just two minor changes. This query links the two joins to the first table instead of on each other. I think this should work better for you!

---

<div class="post-metadata">

**Author:** ![mohamad](https://www.phphelp.com/letter_avatar_proxy/v4/letter/m/ba9def/32.png) [@mohamad](https://www.phphelp.com/u/mohamad)\
**Post date:** [October 18, 2020, 3:38am UTC](https://www.phphelp.com/t/left-join-problem/32318/26 "2020-10-18T03:38:16Z")

</div>

Thanks again,  
Unfortunately, it returns the previous result.  
For finding the problem of the query, maybe it is better to remove (sum) from that to see all of records which are generated after running the query.  
I did it, but I did not understand the problem, probably you can figure it out.  
Kind Regards

---

<div class="post-metadata">

**Author:** ![ErnieAlex](https://www.phphelp.com/letter_avatar_proxy/v4/letter/e/bc8723/32.png) [@ErnieAlex](https://www.phphelp.com/u/ErnieAlex)\
**Post date:** [October 18, 2020, 3:52am UTC](https://www.phphelp.com/t/left-join-problem/32318/27 "2020-10-18T03:52:20Z")

</div>

Well, with the data that you gave me and the query I just posted, The results is this:

| [SUM(W.WASTAGE\_QUANTITY)](http://localhost/phpmyadmin/sql.php?db=test&table=PMT_PRODUCTION_STATISTICS&sql_query=SELECT+%0D%0A++++++++SUM%28W.WASTAGE_QUANTITY%29%2C%0D%0A++++++++SUM%28D.DURATION_DOWNTIME%29%2C%0D%0A++++++++P.PRODUCTION_DATE%2C+%0D%0A++++++++P.PRODUCT_LINE%2C+%0D%0A++++++++SUM%28P.DURATION%29%2C+%0D%0A++++++++SUM%28P.QUANTITY%29%2C+%0D%0A++++++++SUM%28P.NOMINAL_PRODUCTS%29%0D%0A%0D%0AFROM+PMT_PRODUCTION_STATISTICS+AS+P+%0D%0A++++++++LEFT+JOIN+PMT_DOWNTIME+AS+D+%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+D.LINE+AND+P.PRODUCTION_DATE+%3D+D.DATE_DOWNTIME+%0D%0A++++++++LEFT+JOIN+PMT_WASTE+AS+W%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+W.LINE_W+AND+P.PRODUCTION_DATE+%3D+W.DATE_WASTAGE%0D%0AWHERE+P.APP_STATUS+%3D+%27COMPLETED%27+%0D%0AGROUP+BY+P.PRODUCT_LINE++&session_max_rows=25&is_browse_distinct=0) | [SUM(D.DURATION\_DOWNTIME)](http://localhost/phpmyadmin/sql.php?db=test&table=PMT_PRODUCTION_STATISTICS&sql_query=SELECT+%0D%0A++++++++SUM%28W.WASTAGE_QUANTITY%29%2C%0D%0A++++++++SUM%28D.DURATION_DOWNTIME%29%2C%0D%0A++++++++P.PRODUCTION_DATE%2C+%0D%0A++++++++P.PRODUCT_LINE%2C+%0D%0A++++++++SUM%28P.DURATION%29%2C+%0D%0A++++++++SUM%28P.QUANTITY%29%2C+%0D%0A++++++++SUM%28P.NOMINAL_PRODUCTS%29%0D%0A%0D%0AFROM+PMT_PRODUCTION_STATISTICS+AS+P+%0D%0A++++++++LEFT+JOIN+PMT_DOWNTIME+AS+D+%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+D.LINE+AND+P.PRODUCTION_DATE+%3D+D.DATE_DOWNTIME+%0D%0A++++++++LEFT+JOIN+PMT_WASTE+AS+W%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+W.LINE_W+AND+P.PRODUCTION_DATE+%3D+W.DATE_WASTAGE%0D%0AWHERE+P.APP_STATUS+%3D+%27COMPLETED%27+%0D%0AGROUP+BY+P.PRODUCT_LINE++&session_max_rows=25&is_browse_distinct=0) | [PRODUCTION\_DATE](http://localhost/phpmyadmin/sql.php?db=test&table=PMT_PRODUCTION_STATISTICS&sql_query=SELECT+%0D%0A++++++++SUM%28W.WASTAGE_QUANTITY%29%2C%0D%0A++++++++SUM%28D.DURATION_DOWNTIME%29%2C%0D%0A++++++++P.PRODUCTION_DATE%2C+%0D%0A++++++++P.PRODUCT_LINE%2C+%0D%0A++++++++SUM%28P.DURATION%29%2C+%0D%0A++++++++SUM%28P.QUANTITY%29%2C+%0D%0A++++++++SUM%28P.NOMINAL_PRODUCTS%29%0D%0A%0D%0AFROM+PMT_PRODUCTION_STATISTICS+AS+P+%0D%0A++++++++LEFT+JOIN+PMT_DOWNTIME+AS+D+%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+D.LINE+AND+P.PRODUCTION_DATE+%3D+D.DATE_DOWNTIME+%0D%0A++++++++LEFT+JOIN+PMT_WASTE+AS+W%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+W.LINE_W+AND+P.PRODUCTION_DATE+%3D+W.DATE_WASTAGE%0D%0AWHERE+P.APP_STATUS+%3D+%27COMPLETED%27+%0D%0AGROUP+BY+P.PRODUCT_LINE++%0AORDER+BY+%60P%60.%60PRODUCTION_DATE%60+DESC&session_max_rows=25&is_browse_distinct=0) | [PRODUCT\_LINE](http://localhost/phpmyadmin/sql.php?db=test&table=PMT_PRODUCTION_STATISTICS&sql_query=SELECT+%0D%0A++++++++SUM%28W.WASTAGE_QUANTITY%29%2C%0D%0A++++++++SUM%28D.DURATION_DOWNTIME%29%2C%0D%0A++++++++P.PRODUCTION_DATE%2C+%0D%0A++++++++P.PRODUCT_LINE%2C+%0D%0A++++++++SUM%28P.DURATION%29%2C+%0D%0A++++++++SUM%28P.QUANTITY%29%2C+%0D%0A++++++++SUM%28P.NOMINAL_PRODUCTS%29%0D%0A%0D%0AFROM+PMT_PRODUCTION_STATISTICS+AS+P+%0D%0A++++++++LEFT+JOIN+PMT_DOWNTIME+AS+D+%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+D.LINE+AND+P.PRODUCTION_DATE+%3D+D.DATE_DOWNTIME+%0D%0A++++++++LEFT+JOIN+PMT_WASTE+AS+W%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+W.LINE_W+AND+P.PRODUCTION_DATE+%3D+W.DATE_WASTAGE%0D%0AWHERE+P.APP_STATUS+%3D+%27COMPLETED%27+%0D%0AGROUP+BY+P.PRODUCT_LINE++%0AORDER+BY+%60P%60.%60PRODUCT_LINE%60+ASC&session_max_rows=25&is_browse_distinct=0) | [SUM(P.DURATION)](http://localhost/phpmyadmin/sql.php?db=test&table=PMT_PRODUCTION_STATISTICS&sql_query=SELECT+%0D%0A++++++++SUM%28W.WASTAGE_QUANTITY%29%2C%0D%0A++++++++SUM%28D.DURATION_DOWNTIME%29%2C%0D%0A++++++++P.PRODUCTION_DATE%2C+%0D%0A++++++++P.PRODUCT_LINE%2C+%0D%0A++++++++SUM%28P.DURATION%29%2C+%0D%0A++++++++SUM%28P.QUANTITY%29%2C+%0D%0A++++++++SUM%28P.NOMINAL_PRODUCTS%29%0D%0A%0D%0AFROM+PMT_PRODUCTION_STATISTICS+AS+P+%0D%0A++++++++LEFT+JOIN+PMT_DOWNTIME+AS+D+%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+D.LINE+AND+P.PRODUCTION_DATE+%3D+D.DATE_DOWNTIME+%0D%0A++++++++LEFT+JOIN+PMT_WASTE+AS+W%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+W.LINE_W+AND+P.PRODUCTION_DATE+%3D+W.DATE_WASTAGE%0D%0AWHERE+P.APP_STATUS+%3D+%27COMPLETED%27+%0D%0AGROUP+BY+P.PRODUCT_LINE++&session_max_rows=25&is_browse_distinct=0) | [SUM(P.QUANTITY)](http://localhost/phpmyadmin/sql.php?db=test&table=PMT_PRODUCTION_STATISTICS&sql_query=SELECT+%0D%0A++++++++SUM%28W.WASTAGE_QUANTITY%29%2C%0D%0A++++++++SUM%28D.DURATION_DOWNTIME%29%2C%0D%0A++++++++P.PRODUCTION_DATE%2C+%0D%0A++++++++P.PRODUCT_LINE%2C+%0D%0A++++++++SUM%28P.DURATION%29%2C+%0D%0A++++++++SUM%28P.QUANTITY%29%2C+%0D%0A++++++++SUM%28P.NOMINAL_PRODUCTS%29%0D%0A%0D%0AFROM+PMT_PRODUCTION_STATISTICS+AS+P+%0D%0A++++++++LEFT+JOIN+PMT_DOWNTIME+AS+D+%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+D.LINE+AND+P.PRODUCTION_DATE+%3D+D.DATE_DOWNTIME+%0D%0A++++++++LEFT+JOIN+PMT_WASTE+AS+W%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+W.LINE_W+AND+P.PRODUCTION_DATE+%3D+W.DATE_WASTAGE%0D%0AWHERE+P.APP_STATUS+%3D+%27COMPLETED%27+%0D%0AGROUP+BY+P.PRODUCT_LINE++&session_max_rows=25&is_browse_distinct=0) | [SUM(P.NOMINAL\_PRODUCTS)](http://localhost/phpmyadmin/sql.php?db=test&table=PMT_PRODUCTION_STATISTICS&sql_query=SELECT+%0D%0A++++++++SUM%28W.WASTAGE_QUANTITY%29%2C%0D%0A++++++++SUM%28D.DURATION_DOWNTIME%29%2C%0D%0A++++++++P.PRODUCTION_DATE%2C+%0D%0A++++++++P.PRODUCT_LINE%2C+%0D%0A++++++++SUM%28P.DURATION%29%2C+%0D%0A++++++++SUM%28P.QUANTITY%29%2C+%0D%0A++++++++SUM%28P.NOMINAL_PRODUCTS%29%0D%0A%0D%0AFROM+PMT_PRODUCTION_STATISTICS+AS+P+%0D%0A++++++++LEFT+JOIN+PMT_DOWNTIME+AS+D+%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+D.LINE+AND+P.PRODUCTION_DATE+%3D+D.DATE_DOWNTIME+%0D%0A++++++++LEFT+JOIN+PMT_WASTE+AS+W%0D%0A++++++++++++ON+P.PRODUCT_LINE+%3D+W.LINE_W+AND+P.PRODUCTION_DATE+%3D+W.DATE_WASTAGE%0D%0AWHERE+P.APP_STATUS+%3D+%27COMPLETED%27+%0D%0AGROUP+BY+P.PRODUCT_LINE++&session_max_rows=25&is_browse_distinct=0) | |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 108 | 130 | 1399-07-14 00:00:00 | سراک | 3940 | 968000 | 606000 | |
| 10 | 10 | 1399-07-05 00:00:00 | نیک رز1 | 690 | 150000 | 207000 | |

Which appears to be the correct numbers.

---

<div class="post-metadata">

**Author:** ![mohamad](https://www.phphelp.com/letter_avatar_proxy/v4/letter/m/ba9def/32.png) [@mohamad](https://www.phphelp.com/u/mohamad)\
**Post date:** [October 18, 2020, 4:21am UTC](https://www.phphelp.com/t/left-join-problem/32318/28 "2020-10-18T04:21:11Z")

</div>

Dear Ernie,  
For example, SUM(W.WASTAGE\_QUANTITY) must be 54(NOT 108). It means it returns duplicate records.

---

<div class="post-metadata">

**Author:** ![ErnieAlex](https://www.phphelp.com/letter_avatar_proxy/v4/letter/e/bc8723/32.png) [@ErnieAlex](https://www.phphelp.com/u/ErnieAlex)\
**Post date:** [October 18, 2020, 4:39am UTC](https://www.phphelp.com/t/left-join-problem/32318/29 "2020-10-18T04:39:48Z")

</div>

I am heading to bed, it is late in my part of the world.  
I will look into this more in the morning and see if I can figure it out.  
It might be the left-join instead of an inner-join issue. I will have to look at it further when I am not so tired!

Sorry I do not have a simple solution for you yet.

---

<div class="post-metadata">

**Author:** ![ErnieAlex](https://www.phphelp.com/letter_avatar_proxy/v4/letter/e/bc8723/32.png) [@ErnieAlex](https://www.phphelp.com/u/ErnieAlex)\
**Post date:** [October 19, 2020, 1:42am UTC](https://www.phphelp.com/t/left-join-problem/32318/30 "2020-10-19T01:42:47Z")

</div>

Well, Mohamad, my friend, I spent hours on this. Seems that you can not group join’s in that way.  
It causes duplicates and so the sum’s will be wrong. I did some research and experiments and found that you can combine multiple SELECT’s as tables and join them to accomplish the results you need.

After studying a lot for you, I came up with this query. You can adjust it to fit your needs better. My version was checked only for the SUM(WASTE\_QUANTITY) part. You should check the other totals yourself. Hope this helps!

> ```
> > SELECT PRODUCTION_DATE, PRODUCT_LINE,
> > SUM(DURATION) AS SUM_DUR, SUM(QUANTITY) AS SUM_QUA, SUM(NOMINAL_PRODUCTS) AS SUM_NOM,
> > D.SUM_DOWN,
> > W.SUM_WAS
> > FROM PMT_PRODUCTION_STATISTICS AS P
> > 
> > LEFT JOIN (SELECT LINE, SUM(DURATION_DOWNTIME) AS SUM_DOWN 
> > FROM PMT_DOWNTIME 
> > GROUP BY LINE) AS D
> > ON P.PRODUCT_LINE = D.LINE
> > 
> > LEFT JOIN (SELECT LINE_W, SUM(WASTAGE_QUANTITY) AS SUM_WAS
> > FROM PMT_WASTE
> > GROUP BY LINE_W) AS W
> > ON P.PRODUCT_LINE = W.LINE_W
> > 
> > WHERE P.APP_STATUS = 'COMPLETED'
> > GROUP BY PRODUCT_LINE
> 
> ```

I just realized that I did not add the “COMPLETE” section in the WHERE’s. You can add that.  
Whew! Hope this works better for you!

---

<div class="post-metadata">

**Author:** ![mohamad](https://www.phphelp.com/letter_avatar_proxy/v4/letter/m/ba9def/32.png) [@mohamad](https://www.phphelp.com/u/mohamad)\
**Post date:** [October 19, 2020, 4:08am UTC](https://www.phphelp.com/t/left-join-problem/32318/31 "2020-10-19T04:08:30Z")

</div>

Thank you very much, That’s OK,  
Perfect answer

[Previous page](https://www.phphelp.com/t/left-join-problem/32318.md?page=1)