SQL join of a third table with a one two many relationship - Help for a newbie
I am trying to create a sql query for a report that I will eventually create. In our erp software we have a manage shipping transactions screen where you put in a packingslip number and it returns details on the records. For example:
PACKING SLIP Sales Order
PS-000000002511 SO-0001165
PS-000000002512 SO-0001165
PS-000000002500 SO-0001165
I can then double click on a record and it returns the detail. So in my erp software if I select PS-000000002511, it will return 90 records of detail.
I have the following query based off of the two tables that house the packing slip.
SELECT SHIPTRNISSUE.PS_ID, SHIPTRNISSUE.SO_ISSUE_KEY,
FROM DELTEK.SHIP_TRN_ISSUE AS SHIPTRNISSUE INNER JOIN DELTEK.SHIP_TRN AS SHIPTRANS
ON SHIPTRNISSUE.PS_ID = SHIPTRANS.PS_ID
where SHIPTRNISSUE.PS_ID = 'PS-000000002511'
This returns a correct result of PS_ID SO_ISSUE_KEY SO_ID CUST_ID
PS-000000002511 2698 SO-0001165 TEST
The third table I need to add which contains the remaining detail data is called SO_ISSUE_LN. So as stated bove when querying the PS_ID and selecting the sales order number, the returned amount of records coming from the SO_ISSUE_LN table is 90.
The SO_Issue_LN table has the following fields:
SO_ISSUE_KEY, SO_LN_KEY, SO_ID, PS_ID, ISSUE_QTY, SHIP_QTY, AND ITEM__ID
Know being very new to sql my assumption is since I have a relationship of many packing slips (PS_ID) to the same sales order (SO_ID) I need to create some sort of join for this. But everything I have tried did not return my desired result of 90 records. The records seem to duplicate which is an obvious issue with my third join. I've racked my brain for something that I'm sure is so simple but decided to throw in the towel and post my issue.
How do I resolve adding the third table with the correct join/syntax? I hope that makes sense and I provided enough detail.
PACKING SLIP Sales Order
PS-000000002511 SO-0001165
PS-000000002512 SO-0001165
PS-000000002500 SO-0001165
I can then double click on a record and it returns the detail. So in my erp software if I select PS-000000002511, it will return 90 records of detail.
I have the following query based off of the two tables that house the packing slip.
SELECT SHIPTRNISSUE.PS_ID, SHIPTRNISSUE.SO_ISSUE_KEY,
FROM DELTEK.SHIP_TRN_ISSUE AS SHIPTRNISSUE INNER JOIN DELTEK.SHIP_TRN AS SHIPTRANS
ON SHIPTRNISSUE.PS_ID = SHIPTRANS.PS_ID
where SHIPTRNISSUE.PS_ID = 'PS-000000002511'
This returns a correct result of PS_ID SO_ISSUE_KEY SO_ID CUST_ID
PS-000000002511 2698 SO-0001165 TEST
The third table I need to add which contains the remaining detail data is called SO_ISSUE_LN. So as stated bove when querying the PS_ID and selecting the sales order number, the returned amount of records coming from the SO_ISSUE_LN table is 90.
The SO_Issue_LN table has the following fields:
SO_ISSUE_KEY, SO_LN_KEY, SO_ID, PS_ID, ISSUE_QTY, SHIP_QTY, AND ITEM__ID
Know being very new to sql my assumption is since I have a relationship of many packing slips (PS_ID) to the same sales order (SO_ID) I need to create some sort of join for this. But everything I have tried did not return my desired result of 90 records. The records seem to duplicate which is an obvious issue with my third join. I've racked my brain for something that I'm sure is so simple but decided to throw in the towel and post my issue.
How do I resolve adding the third table with the correct join/syntax? I hope that makes sense and I provided enough detail.
ASKER
So do I create two more inner joins?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.
Did you try that?