Left join problem

[benanamen]

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

[ErnieAlex]

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.

[mohamad]

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

[ErnieAlex]

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…

[mohamad]

Thanks a lot. Got it completely.

[mohamad]

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?

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

[mohamad]

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?

[ErnieAlex]

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.

[mohamad]

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

[ErnieAlex]

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

[ErnieAlex]

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)	SUM(D.DURATION_DOWNTIME)	PRODUCTION_DATE	PRODUCT_LINE	SUM(P.DURATION)	SUM(P.QUANTITY)	SUM(P.NOMINAL_PRODUCTS)
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?

[ErnieAlex]

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

[mohamad]

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

[ErnieAlex]

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!

[mohamad]

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

[ErnieAlex]

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

SUM(W.WASTAGE_QUANTITY)	SUM(D.DURATION_DOWNTIME)	PRODUCTION_DATE	PRODUCT_LINE	SUM(P.DURATION)	SUM(P.QUANTITY)	SUM(P.NOMINAL_PRODUCTS)
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.

[mohamad]

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

[ErnieAlex]

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.

[ErnieAlex]

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!

[mohamad]

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