troubleshooting Question

SQL Query Join One table with multiple occurrences of a part number to another that has single occurrences

Avatar of ReneLMadsen
ReneLMadsen asked on
2 Comments1 Solution494 ViewsLast Modified:

I have to build a query that interrogates two tables. THe first table, ArCustStkXref, has columns CustomerCode, StockCode, PartNumber and the second table, invWarehouse, has columns StockCode and Warehouse.

In Table 1 a stock code will occur more than once as part numbers change over time. In Table 2 each StockCode only appears once.

The query I have written is:

SELECT     TOP 100 PERCENT ArCustStkXref.Customer, InvWarehouse.Warehouse, InvWarehouse.StockCode
FROM         ArCustStkXref FULL OUTER JOIN InvWarehouse ON
                      ArCustStkXref.StockCode = InvWarehouse.StockCode
WHERE     (ArCustStkXref.Customer LIKE 'AS001') AND
                      (InvWarehouse.Warehouse LIKE 'FG')

but this returns an instance of invWarehouse.StockCode for every instance of corresponding ArCustStkXref.StockCode (>1000 rows) when I want a single instance of each invWarehouse.StockCode for Customer AS001 - should be around 180 rows.

Thank you,

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros