Solved

Joining tables problem

Posted on 2007-04-06
2
265 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

627 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