Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Transact SQL help needed

Posted on 2006-05-31
3
Medium Priority
?
347 Views
Last Modified: 2008-03-10
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?
0
Comment
Question by:sammaell
  • 2
3 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 total points
ID: 16799301
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
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16799318
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
 

Author Comment

by:sammaell
ID: 16799543
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

578 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