Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 95
  • Last Modified:

SQL Help joining two tables

Good evening. I can do very basic sql syntax. I currently have the following which gives me the result of two tables.

SELECT MH.MO_ID
,MH.BUILD_PART_ID
,MH.BUILD_PART_RVSN_ID
,MH.BLD_PROJ_ID
,MH.BLD_PROJ_ABBRV_CD
,MH.BLD_INVT_ABBRV_CD
,BUILD_PART_KEY
FROM DELTEK.MO_HDR MH
WHERE MO_ID = 'MO-0010720'

SELECT SL.CHG_PROJ_ID
,SL.SO_ID
,SL.SO_LN_NO
,SL.INVT_ABBRV_CD
,SO_LN_KEY
,ITEM_KEY
,ITEM_ID
FROM DELTEK.SO_LN SL
WHERE SO_ID = 'SO-0000954'

One table is sales order header lines and the other is for material orders. I would like to combine these two tables. Essentially the sales order table can have many lines with the same sales order number, there can only be one material order associated with the sales order number. The same would be true for the BLD_PROJ_ID field. I hope that makes sense. I attached a screen shot if that helps.
RESULT.docx
0
DJ P
Asked:
DJ P
  • 3
  • 3
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
You'll get the same results for the material order for each line of the sales order but it's doable:
SELECT SL.CHG_PROJ_ID
,SL.SO_ID
,SL.SO_LN_NO
,SL.INVT_ABBRV_CD
,SO_LN_KEY
,ITEM_KEY
,ITEM_ID
,MH.MO_ID
,MH.BUILD_PART_ID
,MH.BUILD_PART_RVSN_ID
,MH.BLD_PROJ_ID
,MH.BLD_PROJ_ABBRV_CD
,MH.BLD_INVT_ABBRV_CD
,BUILD_PART_KEY
FROM DELTEK.SO_LN SL
join DELTEK.MO_HDR MH on SL.CHG_PROJ_ID = MH.bld_proj_id
WHERE SL.SO_ID = 'SO-0000954'

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Try. I think Kyle got is right. Appending MH.MO_ID = 'MO-0010720' and InnerJoin. Columns you can add/modify based on your need.

SELECT 
  MH.MO_ID
 ,MH.BUILD_PART_ID MH_BUILD_PART_ID
 ,MH.BUILD_PART_RVSN_ID
 ,MH.BLD_PROJ_ID
 ,MH.BLD_PROJ_ABBRV_CD
 ,MH.BLD_INVT_ABBRV_CD
 ,BUILD_PART_KEY
 ,SL.CHG_PROJ_ID SL_CHG_PART_ID
 ,SL.SO_ID
 ,SL.SO_LN_NO
 ,SL.INVT_ABBRV_CD
 ,SL.SO_LN_KEY
 ,SL.ITEM_KEY
 ,SL.ITEM_ID
 FROM DELTEK.MO_HDR MH
INNER JOIN 
 FROM DELTEK.SO_LN SL
ON SL.CHG_PROJ_ID = MH.BLD_PROJ_ID
WHERE MH.MO_ID = 'MO-0010720' AND SL.SO_ID = 'SO-0000954'

Open in new window

0
 
DJ PAuthor Commented:
Pawan,

Just copied and pasted what you have posted and get nothing but errors.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Pawan KumarDatabase ExpertCommented:
Please provide the errors.
0
 
DJ PAuthor Commented:
Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'FROM'.
0
 
Pawan KumarDatabase ExpertCommented:
Ohh my bad !! Please find the modified query below-

SELECT 
  MH.MO_ID
 ,MH.BUILD_PART_ID MH_BUILD_PART_ID
 ,MH.BUILD_PART_RVSN_ID
 ,MH.BLD_PROJ_ID
 ,MH.BLD_PROJ_ABBRV_CD
 ,MH.BLD_INVT_ABBRV_CD
 ,BUILD_PART_KEY
 ,SL.CHG_PROJ_ID SL_CHG_PART_ID
 ,SL.SO_ID
 ,SL.SO_LN_NO
 ,SL.INVT_ABBRV_CD
 ,SL.SO_LN_KEY
 ,SL.ITEM_KEY
 ,SL.ITEM_ID
 FROM DELTEK.MO_HDR MH
INNER JOIN 
 DELTEK.SO_LN SL
ON SL.CHG_PROJ_ID = MH.BLD_PROJ_ID
WHERE MH.MO_ID = 'MO-0010720' AND SL.SO_ID = 'SO-0000954'

Open in new window

0
 
DJ PAuthor Commented:
This is right on the money! Thanks for the assistance.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now