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
Solved

SQL Help joining two tables

Posted on 2016-11-09
7
55 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Loops and updating in SQL Query 9 52
SQL Server 2008 R2, need a pivot/cross tab query... 4 26
TSQL convert date to string 4 34
shrink table after huge delete 2 12
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
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…

808 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