Transact SQL help needed

the goal is to get items with average lead time > 100 days.

i can run this and get desired results:

SELECT     ITEMNMBR, VENDORID, AVRGLDTM / 100 AS Expr1

FROM         IV00103

WHERE     (AVRGLDTM > 10000)

ORDER BY AVRGLDTM DESC

i need to add in two extra columns from different tables (buyerid from table iv00102 and ABC code from table iv00101), so far im at:

SELECT     dbo.IV00103.ITEMNMBR, dbo.IV00103.VENDORID, dbo.IV00103.AVRGLDTM / 100 AS Expr1, dbo.IV00102.BUYERID,
                      dbo.IV00101.ABCCODE
FROM         dbo.IV00103 INNER JOIN
                      dbo.IV00101 ON dbo.IV00103.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
                      dbo.IV00102 ON dbo.IV00103.ITEMNMBR = dbo.IV00102.ITEMNMBR
WHERE     (dbo.IV00103.AVRGLDTM > 10000)
ORDER BY dbo.IV00103.AVRGLDTM DESC

this results in multiple lines with the same information, for example an item number might appear 3 times in the results. what am i doing wrong?
sammaellAsked:
Who is Participating?
 
Brian CroweDatabase AdministratorCommented:
You're not doing anything wrong.  If the same ITENMBR appears multiple times in either of your joined tables then you will have multiple records.  The first step is to better understand your data.  Why would a ITEMNMBR appear multiple times in your joined tables?  Your table names are non-descriptive so I can't really make any guesses on my own.

Below is a possible solution:

SELECT dbo.IV00103.ITEMNMBR,
   dbo.IV00103.VENDORID,
   dbo.IV00103.AVRGLDTM / 100 AS LeadTime,
   dbo.IV00102.BUYERID,
   dbo.IV00101.ABCCODE
FROM dbo.IV00103
INNER JOIN dbo.IV00101
   ON dbo.IV00103.ITEMNMBR = dbo.IV00101.ITEMNMBR
INNER JOIN dbo.IV00102
   ON dbo.IV00103.ITEMNMBR = dbo.IV00102.ITEMNMBR
WHERE dbo.IV00103.AVRGLDTM > 10000
GROUP BY dbo.IV00103.ITEMNMBR,
   dbo.IV00103.VENDORID,
   dbo.IV00103.AVRGLDTM,
   dbo.IV00102.BUYERID,
   dbo.IV00101.ABCCODE
ORDER BY dbo.IV00103.AVRGLDTM DESC
0
 
Brian CroweDatabase AdministratorCommented:
This only works if you have the same ABCCODE / BUYERID for each instance of a ITEMNMBR.  If there are different values then you will still get separate records and you will need to determine how you will select the "correct" ABCCODE / BUYERID value (MIN, MAX, etc.)
0
 
sammaellAuthor Commented:
awesome answer, thankyou! i still get some multiple records of the same itemnmbr, but thats due to different buyerid for an item in multiple locations.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.