Avatar of sammaell
sammaell

asked on 

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?
Microsoft SQL Server

Avatar of undefined
Last Comment
sammaell
ASKER CERTIFIED SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

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.)
Avatar of sammaell
sammaell

ASKER

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.  
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo