February 26, 2010 at 10:31 pm
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 in Advance
Sudheendra
February 26, 2010 at 10:48 pm
Check if this satisfies your requirement..
SELECTProductName
FROM(
SELECT P.ProductName, COUNT(*) MappedCount
FROM ProductDescriptorMapping PDM
INNER JOIN Product P ON PDM.ProductID = P.ProductID
GROUP BY P.ProductName
) P
WHEREMappedCount = ( SELECT COUNT(*) FROM Descriptors )
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 26, 2010 at 11:10 pm
Hi Kingston Dhasian,
thanks for replying.
You didn't understood my problem. I need to join both Product Table and the ProductDescriptor Table through ProductDescriptorMapping table to get only those records whose DescriptorValueId is 1 and 2 and 3. I mean I need to get the result as Maruti in my result set. But my query is returning no values in resultset. I tried your query also, it is also not returning any values.
Thanks
Sudheendra
February 26, 2010 at 11:23 pm
1.
select
p.ProductId
, p.ProductName
, p.CategoryId
from
Product p
where
p.CategoryId = 1
and (select count(distinct DescriptorValueId) from ProductDescriptorMapping pdm where pdm.ProductId = p.ProductId and pdm.DescriptorValueId in (1,2,3)) = 3
2.
select
p.ProductId
, p.ProductName
, p.CategoryId
from
Product p
inner join ProductDescriptorMapping pdm on p.ProductId = pdm.ProductId
where
p.CategoryId = 1
and (pdm.DescriptorValueId =1 or pdm.DescriptorValueId =2 or pdm.DescriptorValueId =3)
group by
p.ProductId
, p.ProductName
, p.CategoryId
having
count(distinct pdm.DescriptorValueId) = 3
is it resolve your problem?
I Have Nine Lives You Have One Only
THINK!
February 26, 2010 at 11:29 pm
It does work in my PC though...
Check this out
WITH cteProduct AS
(
SELECT1 ProductID, 'Maruti' ProductName, 1 CategoryID UNION ALL
SELECT2, 'Scoda', 1 UNION ALL
SELECT3, 'Benz', 1
), cteDescriptors AS
(
SELECT1 DescriptorValueID, 'Black' DescriptorValue UNION ALL
SELECT2, 'Red' UNION ALL
SELECT3, 'Gray'
), cteProductDescriptorMapping AS
(
SELECT1 ProductID, 1 DescriptorValueID UNION ALL
SELECT1, 2 UNION ALL
SELECT1, 3 UNION ALL
SELECT2, 1 UNION ALL
SELECT2, 2 UNION ALL
SELECT3, 1
)
SELECT ProductName
FROM (
SELECT P.ProductName, COUNT(*) MappedCount
FROM cteProductDescriptorMapping PDM
INNER JOIN cteProduct P ON PDM.ProductID = P.ProductID
GROUP BY P.ProductName
) P
WHERE MappedCount = ( SELECT COUNT(*) FROM cteDescriptors )
This does return "Maruti' as the result set
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 27, 2010 at 12:09 am
Hi handkot,
thanks for your reply. The query is working fine.
Thanks & Regards,
Sudheendra
February 27, 2010 at 3:22 am
If you wanted to list all products which have all descriptors, you might use something like this:
DECLARE @product
TABLE (
product_id INTEGER PRIMARY KEY,
product_name VARCHAR(20) NOT NULL,
category_id INTEGER NOT NULL
);
DECLARE @Descriptor
TABLE (
descriptor_id INTEGER PRIMARY KEY,
descriptor_value VARCHAR(20) NOT NULL
);
DECLARE @ProductDescriptorMapping
TABLE (
product_id INTEGER NOT NULL,
descriptor_id INTEGER NOT NULL,
PRIMARY KEY (product_id, descriptor_id)
);
INSERT @product (product_id, product_name, category_id)
VALUES (1, 'Maruti', 1);
INSERT @product (product_id, product_name, category_id)
VALUES (2, 'Scoda', 1);
INSERT @product (product_id, product_name, category_id)
VALUES (3, 'Benz', 1);
INSERT @Descriptor (descriptor_id, descriptor_value)
VALUES (1, 'Black');
INSERT @Descriptor (descriptor_id, descriptor_value)
VALUES (2, 'Red');
INSERT @Descriptor (descriptor_id, descriptor_value)
VALUES (3, 'Grey');
INSERT @ProductDescriptorMapping (product_id, descriptor_id) VALUES (1, 1);
INSERT @ProductDescriptorMapping (product_id, descriptor_id) VALUES (1, 2);
INSERT @ProductDescriptorMapping (product_id, descriptor_id) VALUES (1, 3);
INSERT @ProductDescriptorMapping (product_id, descriptor_id) VALUES (2, 1);
INSERT @ProductDescriptorMapping (product_id, descriptor_id) VALUES (2, 2);
INSERT @ProductDescriptorMapping (product_id, descriptor_id) VALUES (3, 1);
SELECT P.product_id, P.product_name, P.category_id
FROM @product P
JOIN @ProductDescriptorMapping PDM ON PDM.product_id = P.product_id
JOIN @Descriptor D ON D.descriptor_id = PDM.descriptor_id
GROUP BY
P.product_id, P.product_name, P.category_id
HAVING COUNT(D.descriptor_id) = (SELECT COUNT(*) FROM @Descriptor D2);
Results:
product_id product_name category_id
1 Maruti 1
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply