Click here to Skip to main content
14,603,943 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi
I have two tables.

first Loading_Order

1 lodId
2 Lod_Num
3 LodQty

Second table Placement

1 PlaId
2 PLaNum
3 LodNum
4 PlacedQty


I want to create a view where i Can see Remaining QTY (lodQTY - PlacedQTY) is that possible to create MySQL view like that.

Thanks

What I have tried:

select 

t1.LoadingOrderId, 
t1.Location, 
t1.DocType, 
t1.LoadingOderDate, 
t1.LoadingOderNumber, 
t1.Bill_To, 
t1.Valid_From, 
t1.Valid_Till, 
t1.Party_Ref_No, 
t1.Ref_date, 
t1.LDLocation_Name, 	
t1.LDConsignor, 
t1.LDAddress, 
t1.LDCity, 
t1.LDState, 
t1.LDPhone, 
t1.LDLConsignee, 
t1.LDLShip_To_Dest, 
t1.LDLShip_To, 
t1.LDLCity, 
t1.LDLState, 
t1.LDLPhone, 
t1.MDItem, 
t1.MDQuantity, 
t1.MDPakingType, 
t1.Freight_Rate , 
t1.Unit, 
t1.Nos
 
from loadingorder t1 
where  t1.COMP_ID=_COMP_ID 

and    not exists (select *
                  from placement t2
                  where t1.LoadingOderNumber = t2.LoadingOrderNo )
                  
union

Select 
t1.LoadingOrderId, 
t1.Location, 
t1.DocType, 
t1.LoadingOderDate, 
t1.LoadingOderNumber, 
t1.Bill_To, 
t1.Valid_From, 
t1.Valid_Till, 
t1.Party_Ref_No, 
t1.Ref_date, 
t1.LDLocation_Name, 	
t1.LDConsignor, 
t1.LDAddress, 
t1.LDCity, 
t1.LDState, 
t1.LDPhone, 
t1.LDLConsignee, 
t1.LDLShip_To_Dest, 
t1.LDLShip_To, 
t1.LDLCity, 
t1.LDLState, 
t1.LDLPhone, 
t1.MDItem, 
t1.MDQuantity, 
t1.MDPakingType, 
t1.Freight_Rate , 
t1.Unit, 
t1.Nos

from loadingorder t1 INNER JOIN placement Pla ON t1.LoadingOderNumber = Pla.LoadingOrderNo having IFNULL(((t1.MDQuantity)-(Sum(Pla.Quantity))),0) > 0;


but this code is not giving proper result.
Posted
Updated 17-Sep-19 5:37am
Comments
ZurdoDev 13-Sep-19 14:26pm
   
What exactly is your question?
Member 7969814 15-Sep-19 6:38am
   
thanks a lot my dear friend ZurdoDev (MVP) for try to Help Me. My problem is solved by this code:
SELECT a.LoadingOderNumber,a.MDQuantity,b.Quantity, a.MDQuantity-b.Quantity as Difference FROM loadingorder a
left JOIN placement b
ON a.LoadingOderNumber=b.LoadingOrderNo;
ZurdoDev 15-Sep-19 9:18am
   
Please post as solution so that it no longer shows unanswered. Glad to hear you got it working.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

My problem is solved I am using this code and its work fine

SELECT a.LoadingOderNumber,a.MDQuantity,b.Quantity, a.MDQuantity-b.Quantity as Difference FROM loadingorder a
left JOIN placement b
ON a.LoadingOderNumber=b.LoadingOrderNo;
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100