Query to retrieve a column depending on variable value?

  • Hi guys, I have a problema that I can't find a clue to face it:

    The situation:

    I have three different tables for showing product prices as according:

    Customer Info

    Product Info

    Product Price per Customer

    Where:

    - The customer info table stores the customerId and the name

    - The product info stores the product info like productId, productDescription, the family to which the product belongs to AND 10 different prices (price1, price2, price3 and so on)

    - The product Price per customer stores the customerId, the productFamily and the level Price assigned (price1, price2 ... price10)

    So, the point is, that a customer can have different Price levels depending on the family, let's say we have three different families:

    Soda

    Candies

    Cigarrettes

    And Customer 'John Doe' have assigned price1 for Soda Products, price2 for Candies and no Price assigned (normal Price) for Cigarrettes.

    That means that in the database we will have the following info:

    in Customer table:

    CustomerId: 5487

    customerName: 'John Doe'

    in Product Info:

    ProductId | name | familyName | listPrice | price1 | price2 | price3 | price4 | price5

    1 | Pepsi | Soda | 10 | 9 | 8 | 7 | 6 | 5

    2 | Coke | Soda | 10 | 9 | 8 | 7 | 6 | 5

    3 | Fanta | Soda | 10 | 9 | 8 | 7 | 6 | 5

    4 | 7-Up | Soda | 10 | 9 | 8 | 7 | 6 | 5

    5 | Pepsi | Soda | 10 | 9 | 8 | 7 | 6 | 5

    6 | Marlb | Cigarrettes | 10 | 9 | 8 | 7 | 6 | 5

    7 | Camel | Cigarrettes | 10 | 9 | 8 | 7 | 6 | 5

    8 | 7-Up | Soda | 10 | 9 | 8 | 7 | 6 | 5

    9 | Pepsi | Soda | 10 | 9 | 8 | 7 | 6 | 5

    10 | Supe | Candies | 10 | 9 | 8 | 7 | 6 | 5

    11 | Sugar | Candies | 10 | 9 | 8 | 7 | 6 | 5

    Usually, the normal query to retrieve product's prices could be:

    SELECT * FROM productPrice

    Now the problem:

    I need to retrieve productId, productName, familyName and the Price assigned to the customer

    so, in the case of John Doe, I need to retrieve something like

    ProductId | name | familyName | priceAssigned

    1 | Pepsi | Soda | 9

    2 | Coke | Soda | 9

    3 | Fanta | Soda | 9

    4 | 7-Up | Soda | 9

    5 | Pepsi | Soda | 9

    6 | Marlb | Cigarrettes | 10

    7 | Camel | Cigarrettes | 10

    8 | 7-Up | Soda | 10

    9 | Pepsi | Soda | 10

    10 | Supe | Candies | 8

    11 | Sugar | Candies | 8

    Because John have assigned price1 for soda family product, price2 for candies products and pricelist (no assigned Price) for cigarrettes products

    Now, there's any clue how can I run a query that fills the column depending on the Price assigned?

    Problem 1: The way to know which column Price I need to show to the customer is by querying the Price per customer table, Price per customer stores info like following:

    customerId | familyName | priceAssigned

    5423 | Soda | price1

    5423 | Candies | price2

    I was considering to créate a temp table and fill the right Price according to the family Price assigned, but it will consume a lot of performance, so, I was hoping someone out there could give me some light...

    Any help will be appreciated, I accept suggestions on perform a query, créate functions or whatever 🙂

  • Welcome to the forum Henry.

    As this is quite an involved problem, may I suggest that you check out the link in my signature and then post some consumable DDL and DML to set up test tables, data & show sample results.

    If you take the time to do this, I am sure that someone will help you.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • You can do it using Dynamic sql as follows:

    --Setting up Sample Data

    Create Table Ex

    (

    ProductId Int,

    name Varchar(20),

    familyName Varchar(20),

    listPrice Int,

    price1 Int,

    price2 Int,

    price3 Int,

    price4 Int,

    price5 Int

    )

    Insert Into Ex

    Select 1,'Pepsi','Soda',10, 9, 8, 7, 6, 5

    Union ALL

    Select 2,'Coke','Soda',10, 9, 8, 7, 6, 5

    Union ALL

    Select 3,'Fanta','Soda',10, 9, 8, 7, 6, 5

    Union ALL

    Select 4,'7-Up','Soda',10, 9, 8, 7, 6, 5

    Union ALL

    Select 5,'Pepsi','Soda',10, 9, 8, 7, 6, 5

    Union ALL

    Select 6,'Marlb','Cigarrettes', 10, 9, 8, 7, 6, 5

    Union ALL

    Select 7,'Camel','Cigarrettes', 10, 9, 8, 7, 6, 5

    Union ALL

    Select 8,'7-Up','Soda',10, 9, 8, 7, 6, 5

    Union ALL

    Select 9,'Pepsi','Soda',10, 9, 8, 7, 6, 5

    Union ALL

    Select 10, 'Supe','Candies',10, 9, 8, 7, 6, 5

    Union ALL

    Select 11, 'Sugar','Candies',10, 9, 8, 7, 6, 5

    --Query for your requirement

    Declare @Soda Varchar(10), @Candies Varchar(10), @Cigarrettes Varchar(10), @sql Varchar(MAX)

    Select @Soda = 'price1', @Candies = 'price2', @Cigarrettes = 'NULL'

    Select @sql = 'Select ProductId, Name, familyName, (Case When familyName = ''Soda'' Then COALESCE(' + @Soda + ',listPrice)

    When familyName = ''Candies'' Then COALESCE(' + @Candies + ',listPrice)

    When familyName = ''Cigarrettes'' Then COALESCE(' + @Cigarrettes + ',listPrice)

    Else ''''

    END) As PriceAssigned

    From Ex'

    Execute(@sql)

    Hope this was what you were looking for.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • SELECT pi.ProductId, pi.name, pi.familyName,

    CASE pp.priceAssigned

    WHEN 'listPrice' THEN pi.listPrice

    WHEN 'price1' THEN pi.price1

    WHEN 'price2' THEN pi.price2

    WHEN 'price3' THEN pi.price3

    WHEN 'price4' THEN pi.price4

    WHEN 'price5' THEN pi.price5

    END AS [priceAssigned]

    FROM Customer c

    JOIN ProductPricePerCustomer pp ON pp.CustomerId = c.CustomerId

    JOIN ProductInfo pi ON pi.familyName = pp.familyName

    Far away is close at hand in the images of elsewhere.
    Anon.

  • If a sixth column/price gets added or a new product in the family, with 'price 5' rather than 'price5' you will miss data, have to keep maintaining the query. That is the tough part about working with a design that is not normalised.

    ----------------------------------------------------

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply