Left join problem

MySQL database
#1

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

#2

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.

1 Like
#3

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

#4

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

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!

1 Like
#5

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 TABLEbom(idint(11) NOT NULL,material_codetext NOT NULL,material_nametext NOT NULL,product_sizetext NOT NULL,unittext NOT NULL,material_typetext NOT NULL,base_quantitytext NOT NULL,actual_quantitytext NOT NULL,supplierstext NOT NULL,product_codetext NOT NULL,product_linetext NOT NULL,company_nametext NOT NULL,bom_nametext 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

#6

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 exportation_date exportation_quantity
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 material_name
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 material_name total_exportation_quantity
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.

1 Like
#7

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

#8

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

1 Like
#9

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

#10

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…

#11

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

1 Like
#12

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.

1 Like
#13

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 != '' "

if(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

#14

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…

1 Like
#15

Thanks a lot. Got it completely.

#16

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


Very best regards

#17

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?

#18

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.

1 Like
#19

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

#20

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

1 Like
Sponsor our Newsletter | Privacy Policy | Terms of Service