Problem in Join Query - 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)

Problem in Join Query

Author

Topic

sudheendra.b
Starting Member

2 Posts

Posted - 2010-02-27 : 01:19:05

Hello..

I am facing a problem in join the query. i qill explain my problem below in detail.

we have a Product table having fields, ProductId(int), ProductName(Varchar), CategoryId(int)
I have added some sample data in the product table below.

ProductId ProductName CategoryId
1 Maruti 1
2 Scoda 1
3 Benz 1

And also I have one more table called Descriptors having fields DescriptorValueId(int),DescriptorValue(Varchar). I have added some sample data in theDescriptors table below.

DescriptorValueId DescriptorValue
1 Black
2 Red
3 Gray

I have one more table Called ProductDescriptorMapping having fields ProductId(int),DescriptorValueId(int). I have mapped ProductId and DescriptorId in the table as shown below.

ProductId DescriptorValueId
1 1
1 2
1 3
2 1
2 2
3 1

My problem is I need to Select the ProductName with the combinations of DescriptorValueId as (1,2,3).
I have written a join query to fetch the ProductName, but we are getting no records in the ResultSet.
Below in how i have written the query

select Distinct Product.ProductId, Product.ProductName, Product.CategoryId from Product
inner join ProductDescriptorMapping on Product.ProductId = ProductDescriptorMapping.ProductId
where Product.CategoryId = 1 and
ProductDescriptorMapping.DescriptorValueId =1 and ProductDescriptorMapping.DescriptorValueId =2 and ProductDescriptorMapping.DescriptorValueId =3

I want to select such product which is having all the three DescriptorValueIds with CategoryId=1.
But no records are coming in resultset.

Please help me regarding this issue.

Thanks & Regards
Sudheendra

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 01:22:16

[code]
select Product.ProductId, Product.ProductName, Product.CategoryId from Product
inner join ProductDescriptorMapping on Product.ProductId = ProductDescriptorMapping.ProductId
where Product.CategoryId = 1 and
ProductDescriptorMapping.DescriptorValueId IN (1,2,3)
GROUP BY Product.ProductId, Product.ProductName, Product.CategoryId
HAVING COUNT(DISTINCT ProductDescriptorMapping.DescriptorValueId) = 3
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sudheendra.b
Starting Member

2 Posts

Posted - 2010-02-27 : 02:05:26

quote:
Originally posted by visakh16

select Product.ProductId, Product.ProductName, Product.CategoryId from Product
inner join ProductDescriptorMapping on Product.ProductId = ProductDescriptorMapping.ProductId
where Product.CategoryId = 1 and
ProductDescriptorMapping.DescriptorValueId IN (1,2,3)
GROUP BY Product.ProductId, Product.ProductName, Product.CategoryId
HAVING COUNT(DISTINCT ProductDescriptorMapping.DescriptorValueId) = 3

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi visak,

thanks for you reply. The query is perfectly working fine.

Thanks & Regards
Sudheendra

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 02:09:42

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources