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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 URGENT !!! having trouble to make join condition (

Author  Topic 

chulz90
Starting Member

26 Posts

Posted - 2012-11-28 : 22:36:39
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, next
when 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_itemnum

I've some trouble to make join relation in sql query , can you help me to fix this
i want to show locationid in aaa_do table , itemnum in aaa_doline
where aaa_do.do_number=aaa_doline.do_number and
where aaa_dolineitemnum=aaa_mrline.itemnum

i 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_itemnum
from aaa_do aaa
inner join aaa_doline bbb on aaa.do_number = bbb.do_number
inner join aaa_mrline ccc on ccc.mr_number = aaa.mr_number and bbb.dol_itemnum = ccc.mrl_itemnum
Go to Top of Page

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_itemnum
from aaa_do aaa
inner join aaa_doline bbb on aaa.do_number = bbb.do_number
inner 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
Go to Top of Page
   

- Advertisement -