SQL Query Help with a join

Greetings experts. I would like to join three tables together. I have the first two joined correctly (I think). However when I attempt to join the third table my records seem to double. The first syntax that joins my first two tables is shown below. I included some parameters which gives me a result of twelve returned records. I have verified that this is returning the correct amount. The query is as follows:

SELECT PO_RT_COMMIT_DETL.PO_ID AS POID, PO_RT_COMMIT_DETL.PO_RLSE_NO, PO_RT_COMMIT_DETL.PO_LN_KEY, PO_RT_COMMIT_DETL.VEND_NAME,
PO_RT_COMMIT_DETL.PROJ_ID, PO_RT_COMMIT_DETL.ORG_ID, PO_RT_COMMIT_DETL.ACCT_ID,  
PO_RT_COMMIT_DETL.PROJ_NAME, PO_RT_COMMIT_DETL.PO_OPEN_AMT,
PO_RT_COMMIT_DETL.PO_TOT_AMT,  
PO_HDR.S_PO_TYPE, PO_HDR.S_PO_STATUS_TYPE,
PO_HDR.PO_TOT_AMT, PO_HDR.ORD_DT
FROM DELTEK.PO_HDR INNER JOIN DELTEK.PO_RT_COMMIT_DETL ON DELTEK.PO_HDR.PO_ID = DELTEK.PO_RT_COMMIT_DETL.PO_ID
WHERE PROJ_ID = '10371.0001.0004'
AND S_PO_STATUS_TYPE = 'O'
AND PO_OPEN_AMT > '0.00'
AND PO_RT_COMMIT_DETL.PO_ID = 'PO-0051938'
ORDER BY POID

My third table is essential PO LN detail data. This also contains twelve records and the syntax is shown below:

SELECT [PO_ID]
      ,[PO_RLSE_NO]
      ,[PO_LN_KEY]
      ,[PO_LN_NO]
      ,[S_PO_LN_TYPE]
      ,[ITEM_KEY]
      ,[PO_LN_DESC]
      ,[ORD_QTY]
      ,[NET_UNIT_CST_AMT]
      ,[PO_LN_EXT_AMT]
      ,[PO_LN_TOT_AMT]
           
  FROM [DELTEKCP].[DELTEK].[PO_LN]
  where PO_ID = 'PO-0051938'

How do I join this third table to the above without duplicating records?
DJ PAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
Can you show the record set for each of the tables?

In general though if you're duplicating lines you're not joining "enough".

EG:  on that third table you should join on multiple columns. (Most likely PO_ID and line)
0
DJ PAuthor Commented:
Kyle. I'm not sure on the syntax per your comment (sort of a newbie)
0
Kyle AbrahamsSenior .Net DeveloperCommented:
I aliased the tables just for my own sake.  Try updating your select as follows:
SELECT DTL.PO_ID AS POID, DTL.PO_RLSE_NO, DTL.PO_LN_KEY, DTL.VEND_NAME, 
DTL.PROJ_ID, DTL.ORG_ID, DTL.ACCT_ID,  
DTL.PROJ_NAME, DTL.PO_OPEN_AMT, 
DTL.PO_TOT_AMT,  
HDR.S_PO_TYPE, HDR.S_PO_STATUS_TYPE,
HDR.PO_TOT_AMT, HDR.ORD_DT
FROM DELTEK.PO_HDR  HDR
INNER JOIN DELTEK.PO_RT_COMMIT_DETL DTL ON HDR.PO_ID = DTL.PO_ID
-- first attempt is to join on PO_LN_KEY.  IF that doesn't work try by PO_ID and LINE_NUMBER
inner join [DELTEKCP].[DELTEK].[PO_LN] on LN on DTL.[PO_LN_KEY] = LN.[PO_LN_KEY]
WHERE PROJ_ID = '10371.0001.0004'
AND S_PO_STATUS_TYPE = 'O'
AND PO_OPEN_AMT > '0.00'
AND PO_RT_COMMIT_DETL.PO_ID = 'PO-0051938'
ORDER BY POID

Open in new window

0
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

DJ PAuthor Commented:
received and error when trying to run:

An expression of non-boolean type specified in a context where a condition is expected, near 'on'.

inner join [DELTEKCP].[DELTEK].[PO_LN] on LN on DTL.[PO_LN_KEY] = LN.[PO_LN_KEY]
0
SharathData EngineerCommented:
remove the 'ON'

inner join [DELTEKCP].[DELTEK].[PO_LN] LN on DTL.[PO_LN_KEY] = LN.[PO_LN_KEY]
0
DJ PAuthor Commented:
I guess I'm a little confused. Sorry (newbie) I ran this and now get 51,970 rows when the result should only be 12

SELECT DTL.PO_ID AS POID, DTL.PO_RLSE_NO, DTL.PO_LN_KEY, DTL.VEND_NAME,
DTL.PROJ_ID, DTL.ORG_ID, DTL.ACCT_ID,  
DTL.PROJ_NAME, DTL.PO_OPEN_AMT,
DTL.PO_TOT_AMT,
HDR.S_PO_TYPE, HDR.S_PO_STATUS_TYPE,
HDR.PO_TOT_AMT, HDR.ORD_DT
FROM DELTEK.PO_HDR  HDR
INNER JOIN DELTEK.PO_RT_COMMIT_DETL DTL ON HDR.PO_ID = DTL.PO_ID
inner join [DELTEKCP].[DELTEK].[PO_LN] LN on DTL.[PO_LN_KEY] = LN.[PO_LN_KEY]
WHERE PROJ_ID = '10371.0001.0004'
AND S_PO_STATUS_TYPE = 'O'
AND PO_OPEN_AMT > '0.00'
AND  DTL.PO_ID = 'PO-0051938'
0
DJ PAuthor Commented:
If I run it this way I get the 12 rows but not all of the detail is there (meaning missing fields from my original query).

SELECT DTL.PO_ID AS POID, DTL.PO_RLSE_NO, DTL.PO_LN_KEY, DTL.VEND_NAME,
DTL.PROJ_ID, DTL.ORG_ID, DTL.ACCT_ID,  
DTL.PROJ_NAME, DTL.PO_OPEN_AMT,
DTL.PO_TOT_AMT,
HDR.S_PO_TYPE, HDR.S_PO_STATUS_TYPE,
HDR.PO_TOT_AMT, HDR.ORD_DT

FROM DELTEK.PO_HDR  HDR
INNER JOIN DELTEK.PO_RT_COMMIT_DETL DTL ON HDR.PO_ID = DTL.PO_ID
--inner join [DELTEKCP].[DELTEK].[PO_LN] LN on DTL.[PO_LN_KEY] = LN.[PO_LN_KEY]
WHERE PROJ_ID = '10371.0001.0004'
AND S_PO_STATUS_TYPE = 'O'
AND PO_OPEN_AMT > '0.00'
AND  DTL.PO_ID = 'PO-0051938'
0
Pawan KumarDatabase ExpertCommented:
Please try this-

Change INNER JOIN to other joins if required. /*Full or right outer join */

SELECT  *
FROM DELTEK.PO_HDR  HDR
INNER JOIN DELTEK.PO_RT_COMMIT_DETL DTL ON HDR.PO_ID = DTL.PO_ID
INNER join [DELTEKCP].[DELTEK].[PO_LN] LN on DTL.[PO_LN_KEY] = LN.[PO_LN_KEY]
WHERE PROJ_ID = '10371.0001.0004'
AND S_PO_STATUS_TYPE = 'O'
AND PO_OPEN_AMT > '0.00'
AND  DTL.PO_ID = 'PO-0051938'

Open in new window

0
DJ PAuthor Commented:
Pawan, I tried that as well and get 51,971 records. Should be 12.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
What's the relationship between: PO_RT_COMMIT_DETL and PO_LN?

if you do a select top 100 * from each of those how do you correlate the lines?
is DETL to PO_LN a 1 to 1 or 1 to many relationship?  (EG: For each line in DETL, how many lines could / should appear in PO_LN)
0
DJ PAuthor Commented:
Kyle, I'm going to send an attachment with details the hopefully makes things more clear.
0
DJ PAuthor Commented:
See attachment and let me know if that helps at all. Attached is what I am trying to join.
screen-shot-results.docx
0
Kyle AbrahamsSenior .Net DeveloperCommented:
You have to join by both PO ID and the line id.  The issue with the first join which I suggested was that you could have line numbers from multiple POs.

The following should work:

SELECT  *
FROM DELTEK.PO_HDR  HDR
INNER JOIN DELTEK.PO_RT_COMMIT_DETL DTL ON HDR.PO_ID = DTL.PO_ID
INNER join [DELTEKCP].[DELTEK].[PO_LN] LN on DTL.PO_ID = LN.PO_ID AND DTL.[PO_LN_KEY] = LN.[PO_LN_KEY]
WHERE PROJ_ID = '10371.0001.0004'
AND S_PO_STATUS_TYPE = 'O'
AND PO_OPEN_AMT > '0.00'
AND  DTL.PO_ID = 'PO-0051938'

Open in new window



I'm not sure if you have to include the change order number or anything like that as well as I don't know enough about your system.  Might be helpful to do a select where RLSE_NO and / or chng_order_no are not 0.  If you do need to join on those columns as well (and adding them really couldn't hurt) you would need to add them between the first and second table and then again between the 2nd and third table.
0
DJ PAuthor Commented:
Ahh I think I have it now. One last question.  I dropped this in an sql module in my cognos report write and it comes back with an ambiguous column name error yet it runs fine in sql management studio. How do I account for ambiguous column names?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
you have to alias each column or only select it once.

For example:

you have PO_ID  is all 3 tables.

select HDR.PO_ID, dtl.PO_ID ...
this runs fine in SQL.

However the report writer is probably saying I have 2 fields both named PO_ID.  The solution would be to either just select one of them or to alias each column.

select HDR.PO_ID as HDR_PO_ID, dtl.PO_ID as DTL_PO_ID ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DJ PAuthor Commented:
Ok. I was able to get there based on your previous comment with the following (added more columns):
Thanks you so much for the help with the inner join. No way I would have figured that one out as simple as it was (still learning).

SELECT  HDR.PO_ID, HDR.PO_RLSE_NO, HDR.PO_TOT_AMT, DTL.PROJ_ID, DTL.PO_OPEN_AMT, DTL.PO_LN_KEY, DTL.VEND_NAME, LN.APPRVL_DT, LN.ACCPTD_AMT, LN.DUE_DT, LN.ITEM_ID, LN.S_PO_LN_TYPE, LN.PO_LN_DESC, LN.ACCPTD_QTY, LN.RECVD_QTY,
LN.ITEM_RVSN_ID, LN.ORD_DT, LN.NET_UNIT_CST_AMT, LN.PO_LN_UM_CD, HDR.S_PO_STATUS_TYPE
FROM DELTEK.PO_HDR  HDR
INNER JOIN DELTEK.PO_RT_COMMIT_DETL DTL ON HDR.PO_ID = DTL.PO_ID
INNER join [DELTEKCP].[DELTEK].[PO_LN] LN on DTL.PO_ID = LN.PO_ID AND DTL.[PO_LN_KEY] = LN.[PO_LN_KEY]
WHERE HDR.S_PO_STATUS_TYPE = 'O'
AND DTL.PO_OPEN_AMT > '0.00'
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Again just be aware that you may run into issues with change orders.  But best to read up on joins, they're a great tool and I use them on a daily basis in my position as a developer.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.