Solved

Joining tables problem

Posted on 2007-04-06
2
260 Views
Last Modified: 2012-05-05
I have 3 tables :
Products
Categories
UserCategoryDiscounts

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
)
AS  
BEGIN
      DECLARE @user_discount decimal(18,2)
      
      SELECT @user_discount=user_price_mult FROM users WHERE user_ID = @UserID;
      
      IF @user_discount is null
      BEGIN
            SET @user_discount = 1
      END

      
      INSERT @products
      SELECT
      [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]

      FROM
            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))
      RETURN
 
END
0
Comment
Question by:buraksarica
2 Comments
 
LVL 19

Accepted Solution

by:
Melih SARICA earned 500 total points
ID: 18862829
Use this Query Instead..

 INSERT @products
      SELECT  
      [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]
     FROM
            products p left join (select * from user_category_discount  where User_ID=@UserID) ucd
                  on p.product_cat_ID = ucd.category_ID
0
 
LVL 5

Author Comment

by:buraksarica
ID: 18862837
Thank you very much. It works.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question