SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 URGENT !!! having trouble to make join condition (
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chulz90
Starting Member

Indonesia
26 Posts

Posted - 11/28/2012 :  22:36:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/28/2012 :  22:49:58  Show Profile  Reply with Quote
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

Indonesia
26 Posts

Posted - 11/28/2012 :  23:30:17  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000