Existing sales_order table ( order table)
Id, name...
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

Sales_order_line table ( the order list, including ref_sales_order_id with sales_order id)
Id, order id, item number
Id, ref_sales_order_id, quantity

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

Package ( package list , including ref_target_id with the id of the corresponding sales_order_line table)
Id, order detail id
Id, ref_target_id...

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --


The number of packages is equal to the quantity of the order list represents the order detail completed
All order details has complete represents the order

Now want to find out all the completed orders, a great god answers should be how to write, PGSQL is the use

CodePudding user response:

 
- pgSql don't understand, this is SqlService 
Select A. * From sales_order A 
Inner Join sales_order_line B On Anderson, d=B.r ef_sales_order_id 
Inner Join package On B.I C d=C.r ef_target_id And B.q uantity=C.q uantity 

CodePudding user response:

The

reference 1/f, one tree forest _ response:

 
- pgSql don't understand, this is SqlService 
Select A. * From sales_order A 
Inner Join sales_order_line B On Anderson, d=B.r ef_sales_order_id 
Inner Join package On B.I C d=C.r ef_target_id And B.q uantity=C.q uantity 

don't seem to be very right, the final package list without quantity fields, according to the id to the package list of sales_order_line statistical number, I'll look at how to write, thanks to the great god

CodePudding user response:

 
SELECT * 
The FROM sales_order A 
WHERE NOT EXISTS 
(SELECT 1 
The FROM sales_order_line B 
LEFT the JOIN 
AS QTY (SELECT ref_target_id, COUNT (*) 
The FROM package 
GROUP BY ref_target_id) AS C ON B.I D=C.r ef_target_id 
The WHERE (B.Q UANTITY<> C.Q TY OR ISNULL (ref_target_id, ' ')=' ') AND Anderson, D=B.r ef_sales_order_id)