CODEWITHSUNDEEP
sqlsql-server-2005t-sql
Rob Bowman
Rob Bowman

Reputation: 8741

SQL Join to only 1 row - SQL Server 2005

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.

Upvotes: 2

Views: 390

Answers (4)

CResults
CResults

Reputation: 5105

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

Upvotes: 2

AdaTheDev
AdaTheDev

Reputation: 147334

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

Upvotes: 0

burnall
burnall

Reputation: 842

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

Upvotes: 0

van
van

Reputation: 77072

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

Upvotes: 3

Related Questions