TSQL Help

• swestwood

  Newbie

  Points: 6

  More actions

  December 5, 2017 at 3:09 am

  #341914

  Hi I am quite new to SQL and looking for a bit of help from someone who knows more than me.

  I have this query that keeps returning correct results but want to change the results to allow departments as a parameter.

  
SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductId),
             Product.ProductId,
             Product.[Name],
             Price,
             Thumbnail,
             [Image],
             Featured,
             Specials,
             Stock,
     Brand,
             Model,
             Pistons,
             Craft,
             CaliperMaterial,
             PistonMaterial,
             PistonDiameter,
             CaliperLength,
             CaliperWidth,
             CaliperHeight,
     CaliperWeight,
             FinishTreatment,
             PadMaterial,
             PadHeat,
             PadLength,
             PadThickness,
             PadWeight,
             PadFriction,
             PadWidth,
     DiscMaterial,
             DiscCraft,
             DiscPlaneBounce,
             DiscCenterHatMaterial,
             DiscCenterHatCraft,
             BracketMaterial,
             BracketCraft,
             HoseMaterial,
     WheelSize
             FROM   
             Category INNER JOIN
     DepartmentCategory ON Category.CategoryId = DepartmentCategory.CategoryId INNER JOIN
     Department ON DepartmentCategory.DepartmentId = Department.DepartmentId INNER JOIN
     ProductCategory ON Category.CategoryId = ProductCategory.CategoryId INNER JOIN
     Product ON ProductCategory.ProductId = Product.ProductId
WHERE         (ProductCategory.CategoryId = @CategoryId) AND (DepartmentCategory.DepartmentId=@DepartmentId)

  
  This gives me the products that are in categoryId instead of products that are in category and department.
  Any help would be gratefully received or if you need anything else that would help you help me.  Above are the relationships

  Many thanks

• aaron.reese

  SSChampion

  Points: 13498

  More actions

  December 5, 2017 at 6:30 am

  #1970695

  Looking at the ERD there is a many:many relationship between Category and Department (i.e. the same department can appear in many categories - hence the bridging table DepartmentCategory)

  A product is linked to its category via the CarModel but there is no direct relationship for the product to department. Effectively your relationship rules expose the product to every department that is linked to the category so the category ID on the WHERE clause is redundant.
  You need to find a way to directly associate CarModel with Department.  Either a Department ID on CarModel or make Department a 1:many relationship with category (i.e. a sub-category)
  Alternatively try modifying the first part of the WHERE clause to use DepartmentCategory.CategoryID = @CategoryID.