Finding the duplicates - SQL Server Forums

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)

Finding the duplicates

Author

Topic

sachin.hingole
Starting Member

15 Posts

Posted - 2009-05-04 : 06:25:37

Hi,

I have 2 tables
1 is products and 2 is ProductCategory
I want those products having the comman product categories

Product table
ProductID ProductName
1 P1
2 P2
3 P3
4 P4

ProudctCategory Table
ProductCategoryID ProductCategoryname ProductID
1 P1C 1
2 P1C 2
3 P1C 3
4 P2C 1
5 P2C 2
6 P3C 2
7 P3C 3
8 P4C 3

Output
subCat Productid
P1C 1
P2C 1
P1C 2
P2C 2
P3C 2
P3C 3
P4C 3
P1C 3

Thanks in advance

Sachin Hingole

edit: moved to proper forum

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-05-04 : 06:29:44

Will u put some Sample data and expected output!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-04 : 09:32:27

select ProductCategoryname, ProductID
from ProudctCategory as pc
inner join Product as p on p.productid = pc.productid

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2009-05-04 : 13:59:11

select a.ProductCategoryname, a.ProductID from Product p
inner join
(select ProductCategoryID,ProductCategoryname,ProductID from ProudctCategory where ProductCategoryID in
(select ProductCategoryID from ProudctCategory group by ProductCategoryID having count(*)>1)
) a on p.ProductID=a.ProductID
order by a.ProductID

or

select a.ProductCategoryname, a.ProductID from Product p
inner join
(select ProductCategoryID,ProductCategoryname,ProductID from ProudctCategory where ProductCategoryID in
(select ProductCategoryID from ProudctCategory group by ProductCategoryID having count(*)>0)
) a on p.ProductID=a.ProductID
order by a.ProductID

malay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources