Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2010

    Unanswered: Problem facing in Joining the tables


    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

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    SELECT ProductId
         , ProductName
         , CategoryId 
      FROM Product
     WHERE ProductId IN
           ( SELECT ProductId
               FROM ProductDescriptorMapping 
              WHERE Product.CategoryId = 1 
                AND DescriptorValueId IN ( 1,2,3 )
                 BY ProductId
             HAVING COUNT(*) = 3 ) | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts