Joining tables problem

I have 3 tables :

Products have an integer column containing categoryID

UserCategoryDiscounts have 3 columns. UserID, CategoryID, discount

I have a table-returning function that accepts UserID. It is CategoryPricesForUser.

I want it to return a copy of product table, but if there is an entry for a user and category in UserCategoryDiscounts, cut the price with a discount multiplier. But if there is not, retrieve the normal price. there is another user discount multiplier. but it is not very important. (i have a control for null)
Here is my problem : If i call this function with a user have a discount  in UserCategoryDiscount, it is working like a charm. but if i call it with another userID, it DOESN'T retrieve the products in those categories which have "other user" discounts.

Here is my function :

CREATE FUNCTION dbo.CategoryPriceForUser (@UserID int)
RETURNS @products TABLE(
      [product_ID] [int] primary key,
      [product_cat_ID] [int] NULL ,
      [product_code] [nvarchar] (100) COLLATE Turkish_CI_AS NULL ,
      [product_manufacturer_ID] [int] NULL ,
      [product_price] [decimal](18, 2) NULL ,
      [product_image] [nvarchar] (200) COLLATE Turkish_CI_AS NULL ,
      [product_link] [ntext] COLLATE Turkish_CI_AS NULL ,
      [product_date_added] [smalldatetime] NULL ,
      [weight] [nchar] (20) COLLATE Turkish_CI_AS NULL ,
      [discount] [nchar] (20) COLLATE Turkish_CI_AS NULL ,
      [discount_type] [int] NULL ,
      [product_stock] [int] NULL ,
      [newPrice] [decimal](18, 2) NULL ,
      [stock_expected] [nvarchar] (10) COLLATE Turkish_CI_AS NULL ,
      [currency] [nvarchar] (3) COLLATE Turkish_CI_AS NULL ,
      [tax] [nvarchar] (20) COLLATE Turkish_CI_AS NULL
      DECLARE @user_discount decimal(18,2)
      SELECT @user_discount=user_price_mult FROM users WHERE user_ID = @UserID;
      IF @user_discount is null
            SET @user_discount = 1

      INSERT @products
      [product_ID] ,
      [product_cat_ID] ,
      [product_code] ,
      [product_manufacturer_ID]  ,
      [product_price]  ,
      [product_image] ,
      [product_date_added] ,
      [weight] ,
      [discount_type] ,
      [stock_expected] ,
      [currency] ,

            products p left outer join user_category_discount ucd
                  on p.product_cat_ID = ucd.category_ID
      where ((ucd.[user_ID] = @UserID) OR (ucd.[user_ID] is null))
Who is Participating?
Melih SARICAConnect With a Mentor IT ManagerCommented:
Use this Query Instead..

 INSERT @products
      [product_ID] ,   [product_cat_ID] ,   [product_code] ,    [product_manufacturer_ID]  ,   [product_price]  ,
      [product_image] , [product_link],      [product_date_added] ,  [weight] ,  [discount],    [discount_type] ,
      [product_stock],  p.[newPrice]*ISNULL(ucd.mult,1)*@user_discount,[stock_expected] ,     [currency] , [tax]
            products p left join (select * from user_category_discount  where User_ID=@UserID) ucd
                  on p.product_cat_ID = ucd.category_ID
buraksaricaAuthor Commented:
Thank you very much. It works.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.