SQL Join to only 1 row - SQL Server 2005

Question

Hi

I have an AllocatedStock table holding a case number (knows as a TPND) and a quantity. I need to select a list of product stock but present this with the product number (known as TPNB) rather than the case number. I also have a ProductLookup table which holds all TPNBs and TPNDs.

AllocatedStock
AllocatedStockID identity
TPND int
Quantity int

ProductLookup
ProductLookupID identity
TPND int
TPNB int

There are a number of product types (TPNB) that can be provided in more that one case type (TPND). This the required result is total number of each product type held, I used a sum() function as follows:

select TPNB, sum(AllocatedQty) as 'QTY'
from integration.ProductLookup as PL 
inner join  dbo.AllocatedStock as AStock
on PL.TPND = AStock.TPND
group by TPNB

Unfortunately, the ProductLookup table contains some duplicate rows (historic bad data that can't be cleaned up) where a row contains the same TPND and TPNB as another row. The only thing I need to join to the ProductLookup table for is to get the TPNB for the TPND that I have in the AllocatedStock table. Is there any way to get the join to select only the 1st match? The problem I have at present is that for TPNDs that have a duplicate row in the ProductLookup table I get back double the quantity value.

Would be grateful for any help,

Thanks

Rob.

Answer 1

SELECT DISTINCT should do it:

select TPNB, sum(AllocatedQty) as 'QTY'
from (SELECT DISTINCT TPND, TPNB FROM integration.ProductLookup) as PL 
inner join  dbo.AllocatedStock as AStock
on PL.TPND = AStock.TPND
group by TPNB

Answer 2

select distinct tpnb, qty
from (
select TPNB, sum(AllocatedQty) as 'QTY'
from integration.ProductLookup as PL 
inner join  dbo.AllocatedStock as AStock
on PL.TPND = AStock.TPND
group by ProductLookupID ) a

Answer 3

select TPNB, sum(AllocatedQty) as 'QTY'
from 
(
    SELECT TPND, TPNB
    FROM ProductLookup
    GROUP BY TPND, TPNB
) as PL 
inner join  dbo.AllocatedStock as AStock
on PL.TPND = AStock.TPND
group by TPNB

Answer 4

Give this a whirl. I am using a derived query to 'clean' your productlookup table.

 select TPNB, sum(AllocatedQty) as 'QTY'
    from (select distinct TPND, TPNB from integration.ProductLookup) as PL 
    inner join  dbo.AllocatedStock as AStock
    on PL.TPND = AStock.TPND
    group by TPNB