Solved

SQL Help joining two tables

Posted on 2016-11-09
7
64 Views
Last Modified: 2016-11-10
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
Comment
Question by:DJ P
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 41881380
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
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41881644
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
 

Author Comment

by:DJ P
ID: 41882011
Pawan,

Just copied and pasted what you have posted and get nothing but errors.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41882021
Please provide the errors.
0
 

Author Comment

by:DJ P
ID: 41882090
Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'FROM'.
0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41882113
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
 

Author Closing Comment

by:DJ P
ID: 41882599
This is right on the money! Thanks for the assistance.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question