我有三个不同的表格，一个是产品需求表，有需求的数量和价格，第二个是products\u header\u response和order id details，第三个是products\u response表，作为确认数量和价格。现在我想查询总请求数量乘以价格（表示以美元表示的请求产品总额）和确认数量乘以价格（以美元表示的确认产品总额）。但有些订单的金额有误，有些订单的金额有误。

For example correct scenario is total requested quantity values is $200 then confirmed quantity values is also $200 (if we confirm all requested products)
Wrong scenario is requested quantity value is $200 and confirmed quantity values is more than requested quantity value like some $300.

下面是我对连接3个表的查询。

SELECT resp.id,resp.order_number,resp.orderdate,
ROUND(SUM(req.`requested_quantity` * req.`net_price`),2) AS Req_Order_Amnt,
ROUND((SUM(items_resp.dispatch_quantity * items_resp.`net_price`)+ SUM(items_resp.backorder_quantity * items_resp.`net_price`)),2) 
AS Tot_Confirmed_Amnt,
req.edi_order_id,req.requested_quantity
FROM products_header_response resp
JOIN products_request req 
ON req.`edi_order_id` = resp.`order_number`
JOIN products_response items_resp 
ON resp.id= items_resp.`edi_order_response_id` 
WHERE DATE(resp.orderdate) BETWEEN '20180411' AND '20180411'  AND (items_resp.`dispatch_quantity`!='' OR items_resp.`backorder_quantity`!='')
GROUP BY items_resp.`edi_order_response_id`

在产品响应表中，确认的数量将分两列发送，即发货数量和缺货数量。如果存在缺货数量，则在“缺货数量”列中，如果存在发货数量，则在“发货数量”列中，或者如果两个数量都存在，则在两个列中都将发送数量。

For example requested quantity 100, then if dispatch quantity is 50 and backorder quantity is 20  then 50, 20 will be sent in respective columns and 30 will be sent as rejected quantity.

下面两个表的连接工作正常，但从这个连接我只能得到确认的数量。

SELECT resp.id,resp.order_number,resp.orderdate,
ROUND((SUM(items_resp.dispatch_quantity * items_resp.`net_price`) + SUM(items_resp.backorder_quantity * items_resp.`net_price`)),2) 
AS Tot_Confirmed_Amnt
FROM product_header_response resp
JOIN products_response items_resp 
ON resp.id= items_resp.`edi_order_response_id` 
WHERE DATE(resp.orderdate) BETWEEN '20180411' AND '20180411'  
AND (items_resp.`dispatch_quantity`!='' OR items_resp.`backorder_quantity`!='')
GROUP BY items_resp.`edi_order_response_id`

请纠正我的连接哪里做错了。任何帮助都将不胜感激。

products_respose table
id| order_number
3456 ABC123

Products Request table 
edi_order_id| requested_quantity|net_price|product_id
ABC123           2                26.44    pr123 
ABC123           2                22.29    pr234
ABC123           1                12.34    pr321 
ABC123           2                20.05    pr345
ABC123           1                18.26    pr546
ABC123           2                26.48    pr456
ABC123           2                26.44    pr567

产品响应表

dispatch_quantity|backorder_quantity|net_price | product_id | edi_order_resp_id
-                   -                 26.44         pr123         3456
-                   -                 22.29         pr234         3456
-                   -                 12.34         pr321         3456
2                   -                 20.05         pr345         3456
-                   -                 18.26         pr546         3456
2                   -                 26.48         pr456         3456
-                   -                 26.44         pr567         3456

预期结果应为

Order_id | Tot_req_Qty_amount |Tot_confirmed_Qty_amount| Requested_products
ABC123     274.00               93.96                      12

但是我从3个连接中得到了下面错误的结果

Order_id | Tot_req_Qty_amount |Tot_confirmed_Qty_amount| Requested_products
    ABC123     548.00               651.42                      24