# 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:** 20\
**Page:** 1

<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:** [August 5, 2020, 3:38am UTC](https://www.phphelp.com/t/left-join-problem/32318/1 "2020-08-05T03:38:11Z")

</div>

Hello everyone,  
I use 2 queries, and it gives me correct results separately. But when I use **left join** command, it gives wrong results. I cannot understand why does it happen?  
here is the first query:  
`SELECT MATERIAL_NAME, EXPORTATION_DATE AS EXPORTATION_DATE, SUM(EXPORTATION_QUANTITY) AS EXPORTATION_QUANTITY FROM STOCK_INFORMATION GROUP BY MATERIAL_NAME`  
And here is the second query:  
`SELECT SUM(RESULT) AS FINAL, MATERIAL_NAME FROM( SELECT P.PRODUCT_NAME AS PRODUCT_NAME, SUM((B.ACTUAL_QUANTITY * P.PRODUCT_QUANTITY)) AS RESULT ,B.MATERIAL_NAME AS MATERIAL_NAME,B.ACTUAL_QUANTITY, FROM BOM AS B LEFT JOIN PRODUCTION_DATA AS P ON P.PRODUCT_CODE = B.PRODUCT_CODE AND P.BOM_NAME = B.BOM_NAME WHERE MATERIAL_NAME <> '' GROUP BY MATERIAL_NAME, PRODUCT_CODE ) AS RESULT GROUP BY MATERIAL_NAME`

Those are OK, separately, but when I want to left join together, it gives wrong results:  
Here is the final query:

```
SELECT SUM(RESULT) AS FINAL, MATERIAL_NAME, EXPORTATION_QUANTITY FROM(
SELECT S.EXPORTATION_DATE AS EXPORTATION_DATE, SUM(EXPORTATION_QUANTITY) 
AS EXPORTATION_QUANTITY, P.PRODUCT_NAME AS PRODUCT_NAME, 
SUM((B.ACTUAL_QUANTITY * P.PRODUCT_QUANTITY)) AS RESULT ,B.MATERIAL_NAME AS 
MATERIAL_NAME,B.ACTUAL_QUANTITY, FROM BOM AS B LEFT JOIN PRODUCTION_DATA 
AS P ON P.PRODUCT_CODE = B.PRODUCT_CODE AND P.BOM_NAME = B.BOM_NAME LEFT 
JOIN STOCK_INFORMATION AS S ON S.MATERIAL_NAME = B.MATERIAL_NAME WHERE 
MATERIAL_NAME <> '' GROUP BY MATERIAL_NAME, PRODUCT_CODE ) AS RESULT GROUP 
BY MATERIAL_NAME

```

How to get correct feedback from that?  
Looking forward to your comments  
Thanks in advance

---

<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:** [August 5, 2020, 5:37pm UTC](https://www.phphelp.com/t/left-join-problem/32318/2 "2020-08-05T17:37:34Z")

</div>

Well, first you have odd things going on here. In the first query, you assign a field to itself. This is a waste of code and is confusing. Also, you create a RESULT value in more than one place. Also confusing. And, I think you have over-thought the query and joins. You do not need two selects. Perhaps you should list us the table fields in each table and also what you want to get out of the query and then we can help 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:** [August 6, 2020, 3:37am UTC](https://www.phphelp.com/t/left-join-problem/32318/3 "2020-08-06T03:37:29Z")

</div>

Thanks for your time and consideration,  
I have 3 tables (production\_data, BOM, stock\_information)  
In production\_data table, I have some columns like product\_code, product\_quantity, date and bom\_name  
In BOM table, I have some columns like material\_code, product\_code, bom\_name, and material\_quantity.  
In the sotck\_information table, I have some columns like material\_code, date and exportation\_quantity.  
Each product has a specific BOM. When a product is produced, the number of quantity must multiply at its BOM.(Its materials). SO, I have to know this. (sum(product\_quantity\* material\_code))  
Also, I need to control the consumed materials in comparison with exportation\_quantity. (the number of material\_quantity which are exported from warehouse). For example, soft drink has 100 gr sugars, and if we produce 1000 soft drinks, we need to have (100000 gram) sugars. On the other hand, in reality, warehouse gives more than (100000 gram ) sugars. I have to control this.  
The first and second queries returns my targeted results, but in the third query there are something wrongs.  
Again thank you very much for taking your valuable time for this post.  
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:** [August 7, 2020, 1:36am UTC](https://www.phphelp.com/t/left-join-problem/32318/4 "2020-08-07T01:36:15Z")

</div>

Sorry, I was out of town all day. Just got home. It is evening here now, so can help further…

> [@mohamad](#):
>
> SELECT MATERIAL\_NAME, EXPORTATION\_DATE AS EXPORTATION\_DATE, SUM(EXPORTATION\_QUANTITY) AS EXPORTATION\_QUANTITY FROM STOCK\_INFORMATION GROUP BY MATERIAL\_NAME

This first query selects all items in the stock\_information table. It calculates a total based on the quantity  
from each material. That seems logical. Except, you do not need to set the date AS the date and you  
should not set the SUM as the same name. Those will cause issues in complicated joins. It should look  
more like this:  
`SELECT MATERIAL_NAME, EXPORTATION_DATE, SUM(EXPORTATION_QUANTITY) AS TOTAL_EXPORTATION_QUANTITY FROM STOCK_INFORMATION GROUP BY MATERIAL_NAME`  
Minor changes. This lets you know which is the total quantity vs just a quantity.

Now, the second query formatted to be easier to read how the query is laid out looks like this:

> ```
> SELECT SUM(RESULT) AS FINAL, MATERIAL_NAME 
> FROM( 
> SELECT SUM(B.ACTUAL_QUANTITY * P.PRODUCT_QUANTITY) AS RESULT,
> B.MATERIAL_NAME AS MATERIAL_NAME, B.ACTUAL_QUANTITY 
> FROM BOM AS B 
> LEFT JOIN PRODUCTION_DATA AS P 
> ON (P.PRODUCT_CODE = B.PRODUCT_CODE) AND (P.BOM_NAME = B.BOM_NAME) 
> WHERE MATERIAL_NAME <> '' 
> GROUP BY MATERIAL_NAME, PRODUCT_CODE 
> ) AS RESULT 
> GROUP BY MATERIAL_NAME
> 
> ```

It processes two SELECTS and creates two result objects and then sums them. The result object is  
created in two places. Not sure if this is an issue. I do not understand why you select things that are not  
being used in the rest of the query. The results for this query#2 gives you “FINAL” and MATERIAL\_NAME.  
You do not need the other info except if you need to use the fields in calculations. You can remove the  
select for the “PRODUCT\_NAME” since it is not used. Make sense? I am not even sure you need the  
two SELECTS. I would need some live data set up in my local database to test that.

Now, the third query: ( formatted for me to read it easier )

> ```
> SELECT SUM(RESULT) AS FINAL, MATERIAL_NAME, TOTAL_EXPORTATION_QUANTITY 
> FROM (
> SELECT SUM(S.EXPORTATION_QUANTITY) AS TOTAL_EXPORTATION_QUANTITY,
> SUM(B.ACTUAL_QUANTITY * P.PRODUCT_QUANTITY) AS RESULT, 
> B.MATERIAL_NAME AS MATERIAL_NAME, B.ACTUAL_QUANTITY 
> FROM BOM AS B
> LEFT JOIN PRODUCTION_DATA AS P
> ON (P.PRODUCT_CODE = B.PRODUCT_CODE) AND (P.BOM_NAME = B.BOM_NAME) 
> LEFT JOIN STOCK_INFORMATION AS S 
> ON S.MATERIAL_NAME = B.MATERIAL_NAME  
> WHERE MATERIAL_NAME <> ''
> GROUP BY MATERIAL_NAME, PRODUCT_CODE 
> ) AS RESULT 
> GROUP BY MATERIAL_NAME'
> 
> ```

Here, I removed the DATE part since it is NOT used in any other parts of the query and not in the final  
results. Not having data, I had trouble sorting this out. But, I found the error is in the third line. You did  
not add the database name to the “EXPORTATION\_QUANTITY”. Therefore, it was retrieving nothing for  
that field. I added the “S.” to the front of it.  
Try these changes and let us know if it works better for you! Good luck!

---

<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:** [August 7, 2020, 12:07pm UTC](https://www.phphelp.com/t/left-join-problem/32318/5 "2020-08-07T12:07:27Z")

</div>

Dear ErnieAlex, Thank you so much for taking your valuable time,  
The first and second statements return my intended results, but I don’t understand why does not the third statement return a correct results? For example, the TOTAL\_EXPORTATION\_QUANTITY for slat must be 200, but it shows 400. Also, Final is not correct. (However in previous queries, those were OK.)  
Here are my 3 tables.  
`CREATE TABLE`bom`(`id`int(11) NOT NULL,`material\_code`text NOT NULL,`material\_name`text NOT NULL,`product\_size`text NOT NULL,`unit`text NOT NULL,`material\_type`text NOT NULL,`base\_quantity`text NOT NULL,`actual\_quantity`text NOT NULL,`suppliers`text NOT NULL,`product\_code`text NOT NULL,`product\_line`text NOT NULL,`company\_name`text NOT NULL,`bom\_name`text NOT NULL,`description` text NOT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

## – – Dumping data for table `bom`

INSERT INTO `bom` (`id`, `material_code`, `material_name`, `product_size`, `unit`, `material_type`, `base_quantity`, `actual_quantity`, `suppliers`, `product_code`, `product_line`, `company_name`, `bom_name`, `description`) VALUES  
(32, ‘1’, ‘salt’, ‘1500’, ‘gram’, ‘consumable’, ‘50’, ‘75’, ‘zamandi’, ‘251’, ‘serak’, ‘cheshme’, ‘default’, ‘’),(33, ‘’, ‘milk’, ‘1500’, ‘gram’, ‘consumable’, ‘700’, ‘1050’, ‘’, ‘251’, ‘serak’, ‘cheshme’, ‘default’, ‘’),(34, ‘’, ‘nana’, ‘1500’, ‘gram’, ‘consumable’, ‘50’, ‘75’, ‘’, ‘251’, ‘serak’, ‘cheshme’, ‘default’, ‘’),(36, ‘’, ‘label’, ‘1500’, ‘gram’, ‘packaging’, ‘0.02’, ‘0.02’, ‘’, ‘251’, ‘serak’, ‘cheshme’, ‘default’, ‘’),(37, ‘’, ‘water’, ‘1500’, ‘gram’, ‘consumable’, ‘750’, ‘1125’, ‘’, ‘252’, ‘serak’, ‘cheshme’, ‘default’, ‘’),(38, ‘’, ‘milk’, ‘1500’, ‘gram’, ‘consumable’, ‘650’, ‘975’, ‘’, ‘252’, ‘serak’, ‘cheshme’, ‘default’, ‘’),(39, ‘’, ‘hashtgiah’, ‘1500’, ‘gram’, ‘consumable’, ‘17.5’, ‘26.25’, ‘’, ‘252’, ‘serak’, ‘cheshme’, ‘default’, ‘’), (40, ‘’, ‘label’, ‘1500’, ‘gram’, ‘packaging’, ‘0.1’, ‘0.1’, ‘’, ‘252’, ‘serak’, ‘cheshme’, ‘default’, ‘’), (41, ‘’, ‘preform’, ‘1500’, ‘unit’, ‘packaging’, ‘1’, ‘1’, ‘’, ‘252’, ‘serak’, ‘cheshme’, ‘default’, ‘’), (42, ‘’, ‘preform’, ‘1500’, ‘unit’, ‘packaging’, ‘1’, ‘1’, ‘’, ‘251’, ‘serak’, ‘cheshme’, ‘default’, ‘’),(45, ‘’, ‘milk’, ‘1000’, ‘gram’, ‘consumable’, ‘1000’, ‘1000’, ‘’, ‘533’, ‘sidel’, ‘bahar’, ‘1’, ‘’),(46, ‘’, ‘priform 1000’, ‘1000’, ‘unit’, ‘packaging’, ‘1’, ‘1’, ‘’, ‘533’, ‘sidel’, ‘bahar’, ‘1’, ‘’),(47, ‘’, ‘milk’, ‘1500’, ‘gram’, ‘consumable’, ‘800’, ‘1200’, ‘’, ‘251’, ‘serak’, ‘cheshme’, ‘1’, ‘’),  
(48, ‘’, ‘nana’, ‘1500’, ‘gram’, ‘consumable’, ‘98’, ‘147’, ‘’, ‘251’, ‘serak’, ‘cheshme’, ‘1’, ‘’); `

here is the second table  
CREATE TABLE `production_data` (  
`id` int(11) NOT NULL,  
`uid` int(11) NOT NULL,  
`user_recorder` text NOT NULL,  
`product_name` text NOT NULL,  
`product_size` text NOT NULL,  
`product_code` text NOT NULL,  
`product_line` text NOT NULL,  
`pdate` text NOT NULL,  
`shift` text NOT NULL,  
`start_time` time NOT NULL,  
`finish_time` time NOT NULL,  
`duration` text NOT NULL,  
`product_quantity` text NOT NULL,  
`unit` text NOT NULL,  
`reusable_waste` text NOT NULL,  
`unusable_waste` text NOT NULL,  
`bom_name` text NOT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

## – – Dumping data for table `production_data`

INSERT INTO `production_data` (`id`, `uid`, `user_recorder`, `product_name`, `product_size`, `product_code`, `product_line`, `pdate`, `shift`, `start_time`, `finish_time`, `duration`, `product_quantity`, `unit`, `reusable_waste`, `unusable_waste`, `bom_name`) VALUES  
(2, 0, ‘mohamad’, ‘’, ‘’, ‘123’, ‘cheshme’, ‘2020/3/22’, ‘2’, ‘18:00:00’, ‘22:00:00’, ‘04:00:00’, ‘31000’, ‘’, ‘560’, ‘300’, ‘’),  
(3, 0, ‘mohamad’, ‘’, ‘1500’, ‘123’, ‘cheshme’, ‘2020/3/22’, ‘3’, ‘22:00:00’, ‘07:00:00’, ‘09:00:00’, ‘67000’, ‘’, ‘0’, ‘0’, ‘’),  
(4, 0, ‘mohamad’, ‘’, ‘’, ‘254’, ‘serak’, ‘2020/3/22’, ‘3’, ‘22:30:00’, ‘00:30:00’, ‘02:00:00’, ‘5862’, ‘’, ‘0’, ‘0’, ‘’),  
(5, 0, ‘mohamad’, ‘’, ‘’, ‘252’, ‘serak’, ‘2020/3/22’, ‘3’, ‘00:40:00’, ‘07:00:00’, ‘06:20:00’, ‘136138’, ‘’, ‘0’, ‘0’, ‘default’),  
(6, 0, ‘mohamad’, ‘’, ‘1500’, ‘254’, ‘serak’, ‘2020/3/22’, ‘2’, ‘17:51:00’, ‘22:30:00’, ‘04:39:00’, ‘61000’, ‘’, ‘0’, ‘0’, ‘’),  
(7, 0, ‘mohamad’, ‘’, ‘’, ‘251’, ‘serak’, ‘2020/3/22’, ‘1’, ‘07:00:00’, ‘12:45:00’, ‘05:45:00’, ‘45045’, ‘’, ‘0’, ‘0’, ‘1’),  
(8, 0, ‘mohamad’, ‘’, ‘’, ‘251’, ‘serak’, ‘2020/3/21’, ‘2’, ‘20:19:00’, ‘22:30:00’, ‘02:11:00’, ‘8000’, ‘’, ‘0’, ‘0’, ‘default’),  
(9, 0, ‘mohamad’, ‘’, ‘’, ‘251’, ‘serak’, ‘2020/3/21’, ‘3’, ‘22:30:00’, ‘07:00:00’, ‘08:30:00’, ‘108000’, ‘’, ‘0’, ‘0’, ‘default’);

here is the third table:  
CREATE TABLE `stock_information` (  
`id` int(11) NOT NULL,  
`material_code` text NOT NULL,  
`material_name` text NOT NULL,  
`unit` text NOT NULL,  
`material_group` text NOT NULL,  
`importation_date` text NOT NULL,  
`expiration_date` text NOT NULL,  
`importation_quantity` text NOT NULL,  
`suppliers_id` text NOT NULL,  
`suppliers_name` text NOT NULL,  
`exportation_date` text NOT NULL,  
`exportation_quantity` text NOT NULL,  
`exportation_customer_id` text NOT NULL,  
`exportation_customer_name` text NOT NULL,  
`exportation_receiver` text NOT NULL,  
`company` text NOT NULL,  
`description` text NOT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

## – – Dumping data for table `stock_information`

INSERT INTO `stock_information` (`id`, `material_code`, `material_name`, `unit`, `material_group`, `importation_date`, `expiration_date`, `importation_quantity`, `suppliers_id`, `suppliers_name`, `exportation_date`, `exportation_quantity`, `exportation_customer_id`, `exportation_customer_name`, `exportation_receiver`, `company`, `description`) VALUES  
(1, ‘2’, ‘sugar’, ‘’, ‘’, ‘2019/3/21’, ‘2019/3/24’, ‘200’, ‘17’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’),  
(2, ‘2’, ‘sugar’, ‘’, ’ ', ‘2020/3/31’, ‘2020/11/10’, ‘3000’, ‘15’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’),  
(3, ‘1’, ‘milk’, ‘’, ’ ', ‘2020/7/26’, ‘2020/7/29’, ‘125’, ‘20’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’),  
(4, ‘2’, ‘sugar’, ‘’, ’ ', ‘2020/6/28’, ‘2021/2/10’, ‘2500’, ‘15’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’),  
(5, ‘3’, ‘nana’, ‘’, ’ ', ‘2020/5/5’, ‘2020/10/12’, ‘200’, ‘21’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’),  
(6, ‘1’, ‘milk’, ‘’, ’ ', ‘2020/8/30’, ‘2020/9/2’, ‘150’, ‘18’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’),  
(7, ‘1’, ‘milk’, ‘’, ’ ', ‘’, ‘’, ‘’, ‘’, ‘’, ‘2020/5/5’, ‘100’, ‘4’, ‘’, ‘’, ‘’, ‘’),  
(8, ‘1’, ‘milk’, ‘’, ’ ', ‘’, ‘’, ‘’, ‘’, ‘’, ‘2020/8/3’, ‘150’, ‘4’, ‘’, ‘’, ‘’, ‘’),  
(9, ‘1’, ‘milk’, ‘’, ’ ', ‘’, ‘’, ‘’, ‘’, ‘’, ‘2020/6/9’, ‘250’, ‘4’, ‘’, ‘’, ‘’, ‘’),  
(10, ‘2’, ‘sugar’, ‘’, ’ ', ‘’, ‘’, ‘’, ‘’, ‘’, ‘2020/8/2’, ‘1500’, ‘4’, ‘’, ‘’, ‘’, ‘’),  
(11, ‘3’, ‘nana’, ‘’, ’ ', ‘’, ‘’, ‘’, ‘’, ‘’, ‘2020/7/27’, ‘150’, ‘7’, ‘’, ‘’, ‘’, ‘’),  
(12, ‘2’, ‘sugar’, ‘’, ’ ', ‘’, ‘’, ‘’, ‘’, ‘’, ‘2020/5/11’, ‘300’, ‘4’, ‘’, ‘’, ‘’, ‘’),  
(13, ‘1’, ‘milk’, ‘’, ’ ', ‘’, ‘’, ‘’, ‘’, ‘’, ‘2020/10/12’, ‘200’, ‘4’, ‘’, ‘’, ‘’, ‘’),  
(14, ‘3’, ‘nana’, ‘’, ’ ', ‘2020/7/6’, ‘2020/8/23’, ‘250’, ‘21’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’),  
(15, ‘2’, ‘sugar’, ‘’, ’ ', ‘’, ‘’, ‘’, ‘’, ‘’, ‘2020/7/28’, ‘1000’, ‘4’, ‘’, ‘’, ‘’, ‘’),  
(16, ‘2’, ‘sugar’, ‘’, ’ ', ‘’, ‘’, ‘’, ‘’, ‘’, ‘2020/7/25’, ‘200’, ‘4’, ‘’, ‘’, ‘’, ‘’),  
(17, ‘2’, ‘sugar’, ‘’, ’ ', ‘’, ‘’, ‘’, ‘’, ‘’, ‘2020/7/26’, ‘200’, ‘1’, ‘’, ‘’, ‘’, ‘’),  
(18, ‘3’, ‘nana’, ‘’, ’ ', ‘’, ‘’, ‘’, ‘’, ‘’, ‘2020/7/26’, ‘500’, ‘3’, ‘’, ‘’, ‘’, ‘’),  
(19, ‘1’, ‘milk’, ‘’, ’ ', ‘2020/3/21’, ‘2020/3/24’, ‘250’, ‘17’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’),  
(20, ‘1’, ‘milk’, ‘’, ’ ', ‘2020/3/20’, ‘2020/3/24’, ‘500’, ‘17’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’),  
(21, ‘1’, ‘milk’, ‘’, ’ ', ‘’, ‘’, ‘’, ‘’, ‘’, ‘2020/3/21’, ‘70’, ‘4’, ‘’, ‘’, ‘’, ‘’),  
(22, ‘1’, ‘milk’, ‘’, ’ ', ‘’, ‘’, ‘’, ‘’, ‘’, ‘2020/3/22’, ‘100’, ‘4’, ‘’, ‘’, ‘’, ‘’),  
(23, ‘5’, ‘salt’, ‘’, ’ ', ‘’, ‘’, ‘’, ‘’, ‘’, ‘2020/3/21’, ‘200’, ‘4’, ‘’, ‘’, ‘’, ‘’),  
(24, ‘4’, ‘hashtgiah’, ‘’, ’ ', ‘’, ‘’, ‘’, ‘’, ‘’, ‘2020/3/21’, ‘100’, ‘4’, ‘’, ‘’, ‘’, ‘’),  
(25, ‘4’, ‘hashtgiah’, ‘’, ’ ', ‘’, ‘’, ‘’, ‘’, ‘’, ‘2020/3/22’, ‘160’, ‘4’, ‘’, ‘’, ‘’, ‘’);

I was wondering if you could help me with this  
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:** [August 7, 2020, 5:52pm UTC](https://www.phphelp.com/t/left-join-problem/32318/6 "2020-08-07T17:52:23Z")

</div>

I had some time to work on this for you. I created the tables and put in your data. I could not get some of  
the queries to work correctly. I noticed that you had all fields set to text. You can not use math functions  
on text fields. I changed the following fields to work correctly.  
BOM - material\_code, product\_size and product\_code now INT -  
base\_quantity and actual\_quantity now DEC(8,2) Meaning 8 digits with 2 decimals places  
PRODUCTION\_DATA - uid, production\_size, product\_code now INT product\_quantity now DEC(8,2)  
STOCK\_INFORMATION - exportation\_quantity now DEC(8,2)  
\*\*\* It appears to me that you do not understand how to create a database table. We should discuss that  
in another post at some time or you can read more on this in the thousands of articles on the internet.

And, I ran the first query  
in this format:  
SELECT material\_name, exportation\_date, SUM(exportation\_quantity) AS exportation\_quantity FROM stock\_information GROUP BY material\_name  
It worked and gave me this output:

| [material\_name](http://localhost/phpmyadmin/sql.php?db=test&table=stock_information&sql_query=SELECT+material_name%2C+exportation_date%2C+SUM%28exportation_quantity%29+AS+exportation_quantity+FROM+stock_information+GROUP+BY+material_name++%0AORDER+BY+%60stock_information%60.%60material_name%60+ASC&session_max_rows=25&is_browse_distinct=0) | [exportation\_date](http://localhost/phpmyadmin/sql.php?db=test&table=stock_information&sql_query=SELECT+material_name%2C+exportation_date%2C+SUM%28exportation_quantity%29+AS+exportation_quantity+FROM+stock_information+GROUP+BY+material_name++%0AORDER+BY+%60stock_information%60.%60exportation_date%60+ASC&session_max_rows=25&is_browse_distinct=0) | [exportation\_quantity](http://localhost/phpmyadmin/sql.php?db=test&table=stock_information&sql_query=SELECT+material_name%2C+exportation_date%2C+SUM%28exportation_quantity%29+AS+exportation_quantity+FROM+stock_information+GROUP+BY+material_name++%0AORDER+BY+%60exportation_quantity%60+ASC&session_max_rows=25&is_browse_distinct=0) | |
| --- | --- | --- | --- |
| hashtgiah | 2020/3/21 | 260 | |
| milk | | 870 | |
| nana | | 650 | |
| salt | 2020/3/21 | 200 | |
| sugar | | 3200 | |
| The second query was ran using my version: ( Note I removed the product code since it does not work | | | |
| correctly in a group function. It is not needed.) | | | |

> ```
> SELECT SUM(result) AS final, material_name 
> FROM( 
> SELECT SUM(B.actual_quantity * P.product_quantity) AS result,
> B.material_name AS material_name, B.actual_quantity 
> FROM BOM AS B
> LEFT JOIN production_data AS P 
> ON (P.product_code = B.product_code) AND (P.bom_name = B.bom_name) 
> WHERE material_name <> '' 
> GROUP BY material_name
> ) AS result
> GROUP BY material_name
> 
> ```

And, it resulted in this output:

| [final](http://localhost/phpmyadmin/sql.php?db=test&table=stock_information&sql_query=SELECT+SUM%28result%29+AS+final%2C+material_name+%0D%0A++++FROM%28+%0D%0A++++++++SELECT+SUM%28B.actual_quantity+%2A+P.product_quantity%29+AS+result%2C%0D%0A+++++++++++++++++++B.material_name+AS+material_name%2C+B.actual_quantity+%0D%0A++++++++FROM+BOM+AS+B%0D%0A++++++++LEFT+JOIN+production_data+AS+P+%0D%0A++++++++++++ON+%28P.product_code+%3D+B.product_code%29+AND+%28P.bom_name+%3D+B.bom_name%29+%0D%0A++++++++WHERE+material_name+%3C%3E+%27%27+%0D%0A++++++++GROUP+BY+material_name%0D%0A++++%29+AS+result%0D%0A++++GROUP+BY+material_name++%0AORDER+BY+%60final%60+ASC&session_max_rows=25&is_browse_distinct=0) | [material\_name](http://localhost/phpmyadmin/sql.php?db=test&table=stock_information&sql_query=SELECT+SUM%28result%29+AS+final%2C+material_name+%0D%0A++++FROM%28+%0D%0A++++++++SELECT+SUM%28B.actual_quantity+%2A+P.product_quantity%29+AS+result%2C%0D%0A+++++++++++++++++++B.material_name+AS+material_name%2C+B.actual_quantity+%0D%0A++++++++FROM+BOM+AS+B%0D%0A++++++++LEFT+JOIN+production_data+AS+P+%0D%0A++++++++++++ON+%28P.product_code+%3D+B.product_code%29+AND+%28P.bom_name+%3D+B.bom_name%29+%0D%0A++++++++WHERE+material_name+%3C%3E+%27%27+%0D%0A++++++++GROUP+BY+material_name%0D%0A++++%29+AS+result%0D%0A++++GROUP+BY+material_name++%0AORDER+BY+%60result%60.%60material_name%60+ASC&session_max_rows=25&is_browse_distinct=0) | |
| --- | --- | --- |
| 3573622.5 | hashtgiah | |
| 15933.800000000001 | label | |
| 308588550 | milk | |
| 15321615 | nana | |
| 252138 | preform | |
| _NULL_ | priform | |
| 8700000 | salt | |
| 153155250 | water | |
| Now on to the last query. I added in the missing “S” in place and ran it like this: | | |

> ```
> SELECT SUM(result) AS final, material_name, total_exportation_quantity 
> FROM (
> SELECT SUM(S.exportation_quantity) AS total_exportation_quantity,
> SUM(B.actual_quantity * P.product_quantity) AS result, 
> B.material_name AS material_name, B.actual_quantity
> FROM bom AS B
> LEFT JOIN production_data AS P
> ON (P.product_code = B.product_code) AND (P.bom_name = B.bom_name) 
> LEFT JOIN stock_information AS S 
> ON S.material_name = B.material_name  
> GROUP BY material_name
> ) AS result
> GROUP BY material_name
> 
> ```

Here is the results of this query:

| [final](http://localhost/phpmyadmin/sql.php?db=test&table=stock_information&sql_query=SELECT+SUM%28result%29+AS+final%2C+material_name%2C+total_exportation_quantity+%0D%0A++++FROM+%28%0D%0A++++++++SELECT++SUM%28S.exportation_quantity%29+AS+total_exportation_quantity%2C%0D%0A++++++++++++++++SUM%28B.actual_quantity+%2A+P.product_quantity%29+AS+result%2C+%0D%0A++++++++++++++++B.material_name+AS+material_name%2C+B.actual_quantity%0D%0A++++++++FROM+bom+AS+B%0D%0A++++++++LEFT+JOIN+production_data+AS+P%0D%0A++++++++++++ON+%28P.product_code+%3D+B.product_code%29+AND+%28P.bom_name+%3D+B.bom_name%29+%0D%0A++++++++LEFT+JOIN+stock_information+AS+S+%0D%0A++++++++++++ON+S.material_name+%3D+B.material_name++%0D%0A++++++++GROUP+BY+material_name%0D%0A++++%29+AS+result%0D%0A++++GROUP+BY+material_name++%0AORDER+BY+%60final%60+ASC&session_max_rows=25&is_browse_distinct=0) | [material\_name](http://localhost/phpmyadmin/sql.php?db=test&table=stock_information&sql_query=SELECT+SUM%28result%29+AS+final%2C+material_name%2C+total_exportation_quantity+%0D%0A++++FROM+%28%0D%0A++++++++SELECT++SUM%28S.exportation_quantity%29+AS+total_exportation_quantity%2C%0D%0A++++++++++++++++SUM%28B.actual_quantity+%2A+P.product_quantity%29+AS+result%2C+%0D%0A++++++++++++++++B.material_name+AS+material_name%2C+B.actual_quantity%0D%0A++++++++FROM+bom+AS+B%0D%0A++++++++LEFT+JOIN+production_data+AS+P%0D%0A++++++++++++ON+%28P.product_code+%3D+B.product_code%29+AND+%28P.bom_name+%3D+B.bom_name%29+%0D%0A++++++++LEFT+JOIN+stock_information+AS+S+%0D%0A++++++++++++ON+S.material_name+%3D+B.material_name++%0D%0A++++++++GROUP+BY+material_name%0D%0A++++%29+AS+result%0D%0A++++GROUP+BY+material_name++%0AORDER+BY+%60result%60.%60material_name%60+ASC&session_max_rows=25&is_browse_distinct=0) | [total\_exportation\_quantity](http://localhost/phpmyadmin/sql.php?db=test&table=stock_information&sql_query=SELECT+SUM%28result%29+AS+final%2C+material_name%2C+total_exportation_quantity+%0D%0A++++FROM+%28%0D%0A++++++++SELECT++SUM%28S.exportation_quantity%29+AS+total_exportation_quantity%2C%0D%0A++++++++++++++++SUM%28B.actual_quantity+%2A+P.product_quantity%29+AS+result%2C+%0D%0A++++++++++++++++B.material_name+AS+material_name%2C+B.actual_quantity%0D%0A++++++++FROM+bom+AS+B%0D%0A++++++++LEFT+JOIN+production_data+AS+P%0D%0A++++++++++++ON+%28P.product_code+%3D+B.product_code%29+AND+%28P.bom_name+%3D+B.bom_name%29+%0D%0A++++++++LEFT+JOIN+stock_information+AS+S+%0D%0A++++++++++++ON+S.material_name+%3D+B.material_name++%0D%0A++++++++GROUP+BY+material_name%0D%0A++++%29+AS+result%0D%0A++++GROUP+BY+material_name++%0AORDER+BY+%60result%60.%60total_exportation_quantity%60+ASC&session_max_rows=25&is_browse_distinct=0) | |
| --- | --- | --- | --- |
| 7147245.0000 | hashtgiah | 260.00 | |
| 15933.8000 | label | _NULL_ | |
| 3085885500.0000 | milk | 4350.00 | |
| 61286460.0000 | nana | 1950.00 | |
| 252138.0000 | preform | _NULL_ | |
| _NULL_ | priform | _NULL_ | |
| 8700000.0000 | salt | 400.00 | |
| 153155250.0000 | water | _NULL_ | |
| This works without errors, but, does not create the correct value for the FINAL. This is due to the way | | | |
| you are doing the SUM functions. I have to leave for several hours and will look at that problem when | | | |
| I get back. But, we have made headway with fixing your queries. | | | |

---

<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:** [August 8, 2020, 10:03am UTC](https://www.phphelp.com/t/left-join-problem/32318/7 "2020-08-08T10:03:44Z")

</div>

First of all, Thank you very very much for your time and consideration. Words cannot describe your kindness.  
About your first point. Yes, I have all of my data as text, but mathematical operations work correctly and without problem for me. And our results are the same for us. (Is it necessary for me to change structures from text to int while those are correct?)  
Well, I got your results, too. The outputs for the first and second queries are correct, but for the third query both column are wrong. For example, the correct value for total\_exportation\_quantity of salt must be 200 (not 400). So, Final and total\_exportation\_quantity are wrong. How to solve my problem?  
Again, thank you very much for your valuable time.  
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:** [August 9, 2020, 2:19pm UTC](https://www.phphelp.com/t/left-join-problem/32318/8 "2020-08-09T14:19:08Z")

</div>

WOW, I spent two days attempting to fix this. It seems that once you add in a JOIN, the selected data can  
be duplicated and therefore, the sum’s can be duplicated. I finally found an explanation that tells how to fix  
it. I thought I would let you read this and attempt to fix it yourself. Post your tests and let us know if you need  
help with the new query. Hope this explanation helps!  
[https://stackoverflow.com/questions/11364162/mysql-join-and-sum-is-doubling-result](https://stackoverflow.com/questions/11364162/mysql-join-and-sum-is-doubling-result)

---

<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:** [August 10, 2020, 12:24pm UTC](https://www.phphelp.com/t/left-join-problem/32318/9 "2020-08-10T12:24:44Z")

</div>

I’m deeply grateful for your time.  
I tested with the following query, and it returns correct answer. What do you think about my code?  
`SELECT B.MATERIAL_NAME, B.RESULT, S.TOTAL_EXPORTATION_QUANTITY FROM (SELECT B.MATERIAL_NAME,SUM(B.ACTUAL_QUANTITY * P.PRODUCT_QUANTITY) AS RESULT, P.BOM_NAME, P.PRODUCT_CODE FROM PRODUCTION_DATA AS P LEFT JOIN BOM AS B ON P.PRODUCT_CODE = B.PRODUCT_CODE AND P.BOM_NAME = B.BOM_NAME GROUP BY B.MATERIAL_NAME) AS B LEFT JOIN (SELECT SUM(S.EXPORTATION_QUANTITY) AS TOTAL_EXPORTATION_QUANTITY, S.MATERIAL_NAME FROM STOCK_INFORMATION AS S GROUP BY S.MATERIAL_NAME) AS S ON B.MATERIAL_NAME = S.MATERIAL_NAME`  
And what about my another question? Is it necessary to change database structure from text to INT? because, mathematical operations are ok, and we had similar results.  
Looking forward to your comments  
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:** [August 11, 2020, 4:08am UTC](https://www.phphelp.com/t/left-join-problem/32318/10 "2020-08-11T04:08:22Z")

</div>

well, there are errors in that query. I will look at it when I get up tomorrow and see what they are. It appears  
at first look that it has extra sections… Not sure and it is late night here now.

On the other question, well, normally when you create a database, you make all fields that need math used on them as INT or DEC or even FLOAT… If DEC, you set the number of digits and decimals. This is because the database commands work faster when using the correct format. To use text, it must first convert the text into a numeric format and then handle the math requested. This extra conversion process can add more server processing and slow up the server. If you think from another viewpoint, let’s say 1000 users access your page, the server must convert all of the text into numeric format’s 1000 times and then process the math and convert the results back out to text… Hmmm, lots of extra wasted processing going on the server. So, your answer is really yes and no. If it is a small site just for your use only, then leave it as it is if it works. If it is really to learn what to do correctly, then, yes, change it to the correct field formats. Same goes for all the fields. Date fields should be either DATE or DATETIME formats. The database system has special routines built into it just for these fields.

Hope that gets you thinking about everything. More tomorrow on the query…

---

<div class="post-metadata">

**Author:** ![benanamen](https://www.phphelp.com/user_avatar/www.phphelp.com/benanamen/32/287_2.png) [@benanamen](https://www.phphelp.com/u/benanamen)\
**Post date:** [August 11, 2020, 5:06am UTC](https://www.phphelp.com/t/left-join-problem/32318/11 "2020-08-11T05:06:49Z")

</div>

This looks like a good candidate for a CTE Query. (Common Table Expression)

---

<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:** [August 11, 2020, 3:54pm UTC](https://www.phphelp.com/t/left-join-problem/32318/12 "2020-08-11T15:54:49Z")

</div>

Well, I played with the current query you posted. It does work as it should. Although, in my humble opinion it is incorrect. It has two assignments of the same table name. “S”. I don’t understand why it works as is, but, it does. The current query is:

> ```
> SELECT B.material_name, B.result, S.total_exportation_quantity 
> FROM (
> SELECT B.material_name, SUM(B.actual_quantity * P.product_quantity) AS result, P.bom_name, P.product_code 
> FROM production_data AS P 
> LEFT JOIN bom AS B 
> ON P.product_code = B.product_code AND P.bom_name = B.bom_name 
> GROUP BY B.material_name
> ) AS B 
> LEFT JOIN (
> SELECT SUM(S.exportation_quantity) AS total_exportation_quantity, S.material_name 
> FROM stock_information AS S 
> GROUP BY S.material_name
> ) AS S 
> ON B.material_name = S.material_name
> 
> ```

I looked into this last part, the last JOIN. You do not need to assign a table name to it. This is just a left join of a select. Therefore, the select can be normal. Removing this gives us this query:

> ```
> SELECT B.material_name, B.result, S.total_exportation_quantity 
> FROM (
> SELECT B.material_name, SUM(B.actual_quantity * P.product_quantity) AS result, P.bom_name, P.product_code 
> FROM production_data AS P 
> LEFT JOIN bom AS B 
> ON P.product_code = B.product_code AND P.bom_name = B.bom_name 
> GROUP BY B.material_name
> ) AS B 
> LEFT JOIN (
> SELECT SUM(exportation_quantity) AS total_exportation_quantity, material_name 
> FROM stock_information
> GROUP BY material_name
> ) AS S
> ON B.material_name = S.material_name
> 
> ```

This query works as it should, too. But, it fixes the extra table-name assign issue. But, at least you got it working now.

---

<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:** [August 12, 2020, 5:44pm UTC](https://www.phphelp.com/t/left-join-problem/32318/13 "2020-08-12T17:44:42Z")

</div>

Dear ErnieAlex, Thanks for getting back to me. You taught me very practical points which were very perfect.  
My query in php code is ok, but when I use your query(removing s) it gives this error:  
mysqli\_num\_rows() expects parameter 1 to be mysqli\_result.  
So, I have to use my query.  
When I want to add the date to Where in query, it gives wrong result.  
For example I use this query in my php code:  
`$search_query = "SELECT B.MATERIAL_NAME , B.RESULT , S.TOTAL_EXPORTATION_QUANTITY, B.UNIT, B.MATERIAL_TYPE FROM (SELECT B.MATERIAL_NAME,SUM(B.ACTUAL_QUANTITY * P.PRODUCT_QUANTITY) AS RESULT, B.PRODUCT_LINE AS PRODUCT_LINE, B.COMPANY_NAME AS COMPANY_NAME , B.MATERIAL_TYPE AS MATERIAL_TYPE, B.UNIT AS UNIT,P.BOM_NAME, P.PRODUCT_CODE, P.PDATE AS PDATE FROM PRODUCTION_DATA AS P LEFT JOIN BOM AS B ON P.PRODUCT_CODE = B.PRODUCT_CODE AND P.BOM_NAME = B.BOM_NAME GROUP BY B.MATERIAL_NAME) AS B LEFT JOIN (SELECT SUM(S.EXPORTATION_QUANTITY) AS TOTAL_EXPORTATION_QUANTITY, S.MATERIAL_NAME, S.EXPORTATION_DATE AS SDATE FROM STOCK_INFORMATION GROUP BY S.MATERIAL_NAME) AS S ON B.MATERIAL_NAME = S.MATERIAL_NAME WHERE B.MATERIAL_NAME != '' "`

i`f(isset($sdate)) { $search_query .= "AND DATE(PDATE) >= '$sdate' AND DATE(SDATE) "; }`  
It returns incorrect results. (It just removes some of the materials. but, the numbers are incorrect). For example in 2020/3/22 some products have not been manufactured. So, It is clear that, value of B.RESULT should not be equal with previous value of B.RESULT.  
So, above query has some problems. I was wondering if you could help me with this.  
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:** [August 12, 2020, 8:43pm UTC](https://www.phphelp.com/t/left-join-problem/32318/14 "2020-08-12T20:43:59Z")

</div>

If a mysqli\_num\_rows() returns an error saying it expects parameter 1 to be mysqli\_result, this means that the query itself failed. The query has an error in it. There are many ways to test it. First, you can simply debug it by using die($query) just before you run the query to see what is actually in the query. This will show where the error is. Or, you can add error handling that will show the error if it occurs.

Looking at your code, I see you check for a date and then if it exists, you add it to the query. But, you do not add a space before the “AND DATE()” part. This would cause an error. You would need to make that section to include a space first like this: " AND DATE…" so that it will be apart in the query. Common error for most people who build queries optionally.

On the other previous query, you had to do more that just remove one “S” from it. It was removed in several places. Good luck, hope this helps, Ernie…

---

<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:** [August 14, 2020, 10:51am UTC](https://www.phphelp.com/t/left-join-problem/32318/15 "2020-08-14T10:51:31Z")

</div>

Thanks a lot. Got it completely.

---

<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:** [August 20, 2020, 2:17am UTC](https://www.phphelp.com/t/left-join-problem/32318/16 "2020-08-20T02:17:52Z")

</div>

Dear Ernie,  
Would you please have a glimpse at the below topic if you find some time?

> [@How to find appropriate data from the first table and put it in the suitable position in the second table?](https://www.phphelp.com/t/how-to-find-appropriate-data-from-the-first-table-and-put-it-in-the-suitable-position-in-the-second-table/32394):
>
> Hello everyone, I have some code like this: $query = "SELECT M.BEGINNING\_INVENTORY AS BEGINNING\_INVENTORY, S.MATERIAL\_CODE AS MATERIAL\_CODE, S.MATERIAL\_NAME AS MATERIAL\_NAME, SUM(S.IMPORTATION\_QUANTITY) AS SUM\_IMPORTATION, SUM(S.EXPORTATION\_QUANTITY) AS SUM\_EXPORTATION FROM STOCK\_INFORMATION AS S LEFT JOIN MATERIALS AS M ON M.MATERIAL\_CODE = S.MATERIAL\_CODE WHERE S.MATERIAL\_NAME != '' GROUP BY M.MATERIAL\_NAME "; $results2 = mysqli\_query($db, $query); \<the…

Very best regards

---

<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 9, 2020, 1:11pm UTC](https://www.phphelp.com/t/left-join-problem/32318/17 "2020-10-09T13:11:41Z")

</div>

Can you please take a look at this?  
I have a query like this:  
`
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 W.LINE_W = D.LINE AND W.DATE_WASTAGE = D.DATE_DOWNTIME WHERE P.APP_STATUS = ‘COMPLETED’ GROUP BY P.PRODUCT_LINE`

The problem is that, it returns duplicate values and some times the result is 4 times. What is the problem?

Additional information:  
Table 1(PMT\_PRODUCTION\_STATISTICS) has the following columns:  
APP\_STATUS, DURATION, PRODUCT\_LINE, PRODUCTION\_DATE, QUANTITY, NOMINAL\_PRODUCTS  
Table 2 (PMT\_DOWNTIME) has the following columns:  
APP\_STATUS, DATE\_DOWNTIME, DURATION\_DOWNTIME, LINE  
Table 3 (PMT\_WASTE) has the following columns:  
APP\_STATUS, DATE\_WASTAGE, LINE\_W, WASTAGE\_QUANTITY  
What is the problem? How to solve it?

---

<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 10, 2020, 5:27pm UTC](https://www.phphelp.com/t/left-join-problem/32318/18 "2020-10-10T17:27:27Z")

</div>

> [@mohamad](#):
>
> ```
> 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 W.LINE_W = D.LINE AND W.DATE_WASTAGE = D.DATE_DOWNTIME 
> WHERE P.APP_STATUS = 'COMPLETED' 
> GROUP BY P.PRODUCT_LINE
> 
> ```

I formatted it for a display so I could see it’s structure. I would test this inside your SQL tab of your phpMyAdmin panel. If you drop the GROUP-BY line and test in the control panel, you can see exactly what it is returning from your SUM’s. Then, add the grouping back in and see what is wrong. This is hard to test without all of your data. I would suggest testing it without the group-by and see if you missed something. You might need to group it by a different field. Once you see all the data it creates without the grouping, you will be able to see if any of the groups are messed up. Such as a missing product\_line value. You might be getting a null entry for that field in one row which would give you an extra line displayed.  
Not sure if this is clear to you, but, give it a try and let us know what results you receive.

---

<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 13, 2020, 12:38pm UTC](https://www.phphelp.com/t/left-join-problem/32318/19 "2020-10-13T12:38:48Z")

</div>

Thanks for getting back to me;  
I sent My tables data in a private message,  
It looks the query is OK, I would be thankful if you could find some free time to look at that.  
Vary 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 15, 2020, 3:01pm UTC](https://www.phphelp.com/t/left-join-problem/32318/20 "2020-10-15T15:01:38Z")

</div>

Sorry, Mohamad, I was out of town. I will look at this today for you!

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