Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    1

    Unanswered: Summary with many-to-many

    Hi.

    Need some help in creating view:

    I have three tables:

    1. Product
    2. ProductAccessories
    3. Accessories

    I have to create a view which gives most fields in Product and a last Boolean (bit) field which tells me whether the product contains a specific accessory. I should have all records in Product appearing once.

    Thanks for your replies.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You didn't gave us a lot to go on, so I made some assumptions.
    Code:
    CREATE VIEW MyView
    AS
    SELECT Product.Column1, 
       Product.Column2, 
       ..., 
       CASE WHEN Accessories.Id IS NULL 
          THEN 0 
          ELSE 1 
       END as IndHasSpecificAccessory 
    FROM Product
        LEFT OUTER JOIN ProductAccessories ON 
           Product.Id = ProductAccessories.ProductId
        LEFT OUTER JOIN Accessories ON 
           ProductAccessories.AccessoryId = Accessories.Id AND
           Accessories.Name = 'The specific accessory I am interested in'
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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