Joining to a table with duplicates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Joining to a table with duplicates

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-02-12 : 11:15:55

Hi i have a view made up of product and supplier tables. This give me back the columns i need the join is on product_code, between the product and supplier tables.

I now want to use this view to join to the price table. But the issues am having is that my price table has all the changes for the products so for example i have 1 product in the price table 3 times which will have 3 different prices it has a date field in it also to show when the last price has made. So when i join my view above to the price table i have duplicate product_code in this view because i will have many different prices for each product.

Any help would be great thanks..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 11:32:27

SELECT *
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY p.product_code ORDER BY pr.datefield DESC) AS RowNo,
p.product_code,s.supplier_code,pr.price,....
FROM product p
INNER JOIN supplier s
ONN s.product_code=p.product_code
INNER JOIN price pr
ON pr.product_code=p.product_code
)t
WHERE t.RowNo=1

i have assumed price table contains product_code field (replace it with your linking column). Also add all columns you wantedinisde subquery (in pace of ...) and also in place of * outside.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-02-12 : 11:57:52

Hey i've done this amd made the cchanges but am getting a erroe on row_number..

'ROW_NUMBER' is not a recognized function name.

dewacorp.alliances

452 Posts

Posted - 2008-02-12 : 12:01:17

quote:

'ROW_NUMBER' is not a recognized function name.

Are you using SQL2000 ... aren't you? This functionality is only for SQL2005.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 12:06:29

Then try this:-

SELECT *
FROM
(SELECT (SELECT COUNT(*) + 1 FROM price WHERE product_code=pr.product_code AND datefield > pr.datefield) AS RowNo,
p.product_code,s.supplier_code,pr.price,....
FROM product p
INNER JOIN supplier s
ONN s.product_code=p.product_code
INNER JOIN price pr
ON pr.product_code=p.product_code
)t
WHERE t.RowNo=1

i had given a 2005 compatible soln as this was posted in 2005 forum

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-12 : 12:08:58

Perhaps you could use the following derived table to join to your view?


(select a.PriceId, a.ProductId, a.Price
from Price as a
where a.CreationStamp = (select max(CreationStamp)
	from Price as b
	where a.ProductId = b.ProductId)) as c

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources