Solved

Joining tables problem

Posted on 2007-04-06
2
263 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

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.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

739 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