Select from two tables with filtered duplicate row

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)

Select from two tables with filtered duplicate row

Author

Topic

viccan1
Starting Member

6 Posts

Posted - 2009-07-21 : 04:30:16

Hei,

I have two tables Product (Id (Pk), PlaceId, ProductName, ProductDesc) and Shop (ShopId(Pk), ShopNumber, Name, Date)
What I want is to get all data/columns from Product and Shop table based on relation PlaceId -> ShopNumber (one-to-many, there is no defined any link between these two tables) BUT without repeated rows which may come from Shop table.
F.eks for one PlaceId from Product table may correspond several ShopNumber in Shop table. I’d like to get the one with the highest ShopId.

I tried Select with Left Join but cannot filter the data which comes from Shop table so im getting always duplicate rows.

Product

Id | PlaceId | ProductName | ProductDesc
-----------------------------------------
P1 | 21 | Prod1 | ProductDesc1
P2 | 22 | Prod2 | ProductDesc2
P3 | 23 | Prod3 | ProductDesc3
P4 | 24 | Prod4 | ProductDesc4

Shop

ShopId | ShopNumber | Name | Date
-----------------------------------------
1 | 21 | N1 | 11.2006
2 | 22 | N2 | 11.2006
3 | 22 | N3 | 14.2006
4 | 23 | N4 | 13.2006
5 | 24 | N5 | 21.2006

Result

Id | PlaceId | ShopNumber | ShopId | Name | ProductName | ProductDesc |
-------------------------------------------------------------------------
P1 | 21 | 21 | 1 | N1 | Prod1 | ProductDesc1 |
P2 | 22 | 22 | 3 | N3 | Prod2 | ProductDesc2 |
P3 | 23 | 23 | 4 | N4 | Prod3 | ProductDesc3 |
P4 | 24 | 24 | 5 | N5 | Prod4 | ProductDesc4 |

Any ideas?
Thanks!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-21 : 04:36:59

Post what you have got currently and an example of the results you are looking for, it will be easier to help you then.

I am guessing you just need to make your join table a derived table, but can't be sure without seeing some sample data.

viccan1
Starting Member

6 Posts

Posted - 2009-07-21 : 06:06:58

quote:
Originally posted by RickD

Post what you have got currently and an example of the results you are looking for, it will be easier to help you then.

I am guessing you just need to make your join table a derived table, but can't be sure without seeing some sample data.

posted it.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-21 : 06:11:51

[code]
select t.id,t.placeid,s.shopid,s.shopnumber,s.name,t.ProductName, t.ProductDesc from product t
inner join (select row_number()over(partition by shopnumber order by shopid desc)as rid,* from shop)s
on s.shopnumber = t.placeid and s.rid = 1
[/code]

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-21 : 06:14:53

Or:

select p.id,p.PlaceId,s.ShopNumber,s.ShopId,s.[Name],p.ProductName,p.ProductDesc
from Product p
inner join (select ShopNumber,max(ShopId) ShopId, max([Name]) as [Name] from Shop group by ShopNumber) s
on s.ShopNumber = p.PlaceId

viccan1
Starting Member

6 Posts

Posted - 2009-07-21 : 08:49:28

The problem is that Shop table has over 3 mln rows and these two

select ShopNumber,max(ShopId) ShopId, max([Name]) as [Name] from Shop group by ShopNumber

select row_number()over(partition by shopnumber order by shopid desc)as rid,* from shop

works very slow.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-21 : 09:00:49

Do you have any indexes on the tables?

Put a clustered index on ShopId if it is a unique number.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources