Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Hello guys i've trouble to make join condition. can u help me to fix this???this the procedure MR (Material Request) --approved--> DO (Delivery Order) --approved--> DN (Delivery Note)There're 15 qty of my stock item, when 1st MR line contain 10 qty and 2nd MR line contain 10 qty, that MR number will become a part of Delivery Order (DO)if that MR has approved.When i generate 1st DO_NUMBER depend form 1st MR_NUMBER which contain 10 qty, nextwhen i generate 2st DO_NUMBER depend from 2nd MR_NUMBER which contain 10 qty too.Both of them will become a part of Delivery Note (DN) if that DO has approved.if 1st DN contain 1st DO and 2nd DN contain 2nd DO so my stock item will become in below zero digit.So i want to show what kind of itemnum which is has reserved/booked by last DO and the location where it reserved/booked in DO form,so my stock item couldn't be in below zero digit.a. DO table (aaa_do) *do_number do_mr_number do_locationid b. DO LINE table (aaa_doline) *dol_do_number dol_itemnum c. MR table (aaa_mr) *mr_number d. MR LINE table (aaa_mrline) *mrl_mr_number mrl_itemnumI've some trouble to make join relation in sql query , can you help me to fix thisi want to show locationid in aaa_do table , itemnum in aaa_dolinewhere aaa_do.do_number=aaa_doline.do_number andwhere aaa_dolineitemnum=aaa_mrline.itemnumi hope u can help me to fix this...
sodeep
Master Smack Fu Yak Hacker
7174 Posts
Posted - 2012-11-28 : 22:49:58
Where do you store DN and any column for Approval?Here is my shot:
Select aaa.LocationId,bbb.dol_itemnumfrom aaa_do aaainner join aaa_doline bbb on aaa.do_number = bbb.do_numberinner join aaa_mrline ccc on ccc.mr_number = aaa.mr_number and bbb.dol_itemnum = ccc.mrl_itemnum
chulz90
Starting Member
26 Posts
Posted - 2012-11-28 : 23:30:17
quote:Originally posted by sodeep Where do you store DN and any column for Approval?Here is my shot:
Select aaa.LocationId,bbb.dol_itemnumfrom aaa_do aaainner join aaa_doline bbb on aaa.do_number = bbb.do_numberinner join aaa_mrline ccc on ccc.mr_number = aaa.mr_number and bbb.dol_itemnum = ccc.mrl_itemnum
Every table have a coloumn for approval, i just focus in DO. but thanks for the script