none
How can I return a data that uses temporary values from another table RRS feed

  • Question

  • I have a table of items that sometimes we temperarly change the value of either by item or by type.  This is then used to fill GridView on a website. 

    To use as an example and for testing I have created these two tables.

    Items

    Make Model Value
    Ford Mustang 20000
    Ford Tauras 15000
    Dodge Challenger 27000
    Chevy Camaro 28000
    Chevy Corvette 40000

    Superceed

    Make Model Value
    Ford   5000
    Chevy   7500
      Corvette 50000

    So all of the Fords will show as 5000. The chevy's will be at 7500 but the Corvette will go up to 50000.  So would first superceed the most general and then the more specific.

    Results

    Make Model Value
    Ford Mustang 5000
    Ford Tauras 5000
    Dodge Challenger 27000
    Chevy Camaro 7500
    Chevy Corvette 50000

    Thank you in advance for any advice you can give me.

    Tuesday, August 9, 2011 1:22 PM

Answers

  • Are you looking for something like this?

    declare @items table (make varchar(30), model varchar(30), value int)
    insert @items select 'Ford','Mustang',20000
      union all select 'Ford','Taurus',15000
      union all select 'Dodge','Challenger',27000
      union all select 'Chevy','Camaro',28000
      union all select 'Chevy','Corvette',40000
    
    declare @supercede table (make varchar(30), model varchar(30), value int)
    insert @supercede select 'Ford','',5000
        union all select 'Chevy','',7500
        union all select '','Corvette',50000
    
    
    select make
       ,model
       ,value=coalesce(makemodeloverride,modeloverride,makeoverride,value)
    from @items i
    outer apply (select makemodeloverride=value
           from @supercede
           where make=i.make and model=i.model) F1
    outer apply (select modeloverride=value
           from @supercede
           where model=i.model) F2
    outer apply (select makeoverride=value
           from @supercede
           where make=i.make) F3
    /*
    make model   value
    ----- ---------- -----
    Ford Mustang   5000
    Ford Taurus   5000
    Dodge Challenger 27000
    Chevy Camaro   7500
    Chevy Corvette  50000
    */
    

    If it finds a SuperCede row with make AND model, then it uses that value; otherwise if it finds a row with model only, then it uses that value; otherwise if it finds a row with make only, it uses that value; otherwise it just uses the value defined in the Items table.  This is a perfect example of using the COALESCE function.

    Note: This will only work in SQL2005 and beyond (because of the OUTER APPLY).

     


    --Brad (My Blog)
    • Proposed as answer by Stephanie Lv Wednesday, August 10, 2011 5:25 AM
    • Marked as answer by DChiShaggy Monday, August 15, 2011 10:47 AM
    Tuesday, August 9, 2011 4:43 PM
    Moderator

All replies

  • Assuming that it is a two level system you could say

    Update Items

    set value = NewValue

    from items join ( select value as newvalue from Superceed S1 where s1.make = items.make)

     

    Update Items

    set value = NewValue

    from items join ( select value as newvalue from Superceed S1 where s1.model = items.modal)

     

    however I don't think it's going to be that easy

     

    in which case I think you are going to need a cursor to process each line of superceed in order.

     


    Alun Davies
    Tuesday, August 9, 2011 2:58 PM
  • Are you looking for something like this?

    declare @items table (make varchar(30), model varchar(30), value int)
    insert @items select 'Ford','Mustang',20000
      union all select 'Ford','Taurus',15000
      union all select 'Dodge','Challenger',27000
      union all select 'Chevy','Camaro',28000
      union all select 'Chevy','Corvette',40000
    
    declare @supercede table (make varchar(30), model varchar(30), value int)
    insert @supercede select 'Ford','',5000
        union all select 'Chevy','',7500
        union all select '','Corvette',50000
    
    
    select make
       ,model
       ,value=coalesce(makemodeloverride,modeloverride,makeoverride,value)
    from @items i
    outer apply (select makemodeloverride=value
           from @supercede
           where make=i.make and model=i.model) F1
    outer apply (select modeloverride=value
           from @supercede
           where model=i.model) F2
    outer apply (select makeoverride=value
           from @supercede
           where make=i.make) F3
    /*
    make model   value
    ----- ---------- -----
    Ford Mustang   5000
    Ford Taurus   5000
    Dodge Challenger 27000
    Chevy Camaro   7500
    Chevy Corvette  50000
    */
    

    If it finds a SuperCede row with make AND model, then it uses that value; otherwise if it finds a row with model only, then it uses that value; otherwise if it finds a row with make only, it uses that value; otherwise it just uses the value defined in the Items table.  This is a perfect example of using the COALESCE function.

    Note: This will only work in SQL2005 and beyond (because of the OUTER APPLY).

     


    --Brad (My Blog)
    • Proposed as answer by Stephanie Lv Wednesday, August 10, 2011 5:25 AM
    • Marked as answer by DChiShaggy Monday, August 15, 2011 10:47 AM
    Tuesday, August 9, 2011 4:43 PM
    Moderator
  • Actually there is an even simpler solution using a single LEFT OUTER JOIN.

     

    -- Using Brad's test data --
    declare @items table (make varchar(30), model varchar(30), value int)
    insert @items select 'Ford','Mustang',20000
     union all select 'Ford','Taurus',15000
     union all select 'Dodge','Challenger',27000
     union all select 'Chevy','Camaro',28000
     union all select 'Chevy','Corvette',40000
    
    declare @supercede table (make varchar(30), model varchar(30), value int)
    insert @supercede select 'Ford','',5000
     union all select 'Chevy','',7500
     union all select '','Corvette',50000
     
    -- Solution--
    SELECT 
    i.make,
    i.model,
    CASE WHEN s.make IS NULL OR s.model IS NULL THEN i.value ELSE s.value END AS Value
    FROM @items i
    LEFT OUTER JOIN @supercede s 
    	ON i.make = s.make OR i.model = s.model
    

    Plus it will work in any version of SQL Server...

     


    Jason Long
    Tuesday, August 9, 2011 5:04 PM
  • I'm afraid that won't work because it could (and, in fact, does) pull in multiple rows from the @supercede table.

    If you run the LEFT JOIN query, you will end up with two rows for the Corvette... One with a value of 7500 and one with a value of 50000.

     


    --Brad (My Blog)
    Tuesday, August 9, 2011 5:07 PM
    Moderator
  • Good catch Brad! You are correct.
    Jason Long
    Tuesday, August 9, 2011 5:11 PM
  • The following correction to the join fixes the problem...

    SELECT 
    i.make,
    i.model,
    CASE WHEN s.make IS NULL OR s.model IS NULL THEN i.value ELSE s.value END AS Value
    FROM @items i
    LEFT OUTER JOIN @supercede s 
    	ON i.model = s.model 
    	OR i.make = CASE WHEN s.model IS NULL THEN s.make ELSE '' END
    

    Looking at the execution plans, this method outperforms the outer apply method by a pretty wide margin. (25% of batch v/s 40% of batch)


    Jason Long
    Tuesday, August 9, 2011 5:19 PM
  • If you run that query, then the Fords don't get a value of 5000 and the Chevy Camaro does not get the value of 7500.

    But that's because I put empty strings in my tables as opposed to NULLs.

    If you change your condition to:

    OR i.make=CASE WHEN s.model='' THEN s.make ELSE '' END

    Then it will fix that... but it still has the problem of two rows for the Corvette.

     


    --Brad (My Blog)
    Tuesday, August 9, 2011 5:24 PM
    Moderator
  • Yea... You are, once again, correct. The more I look at it the more evident it is that it can't be done without 2 passes at the @supercede table.

    This was the cleanest I could get it. (and actually get the correct results)

     

    <pre lang="x-sql">
    
    -- Test data --
    declare @items table (make varchar(30), model varchar(30), value int)
    insert @items select 'Ford','Mustang',20000
     union all select 'Ford','Taurus',15000
     union all select 'Dodge','Challenger',27000
     union all select 'Chevy','Camaro',28000
     union all select 'Chevy','Corvette',40000
    
    declare @supercede table (make varchar(30), model varchar(30), value int)
    insert @supercede select 'Ford',NULL,5000
      union all select 'Chevy',NULL,7500
      union all select NULL,'Corvette',50000
    
    -- Note: using NULLs instead of empty strings --  
      
    -- Solution--
    SELECT 
    i.make,
    i.model,
    COALESCE(s2.value, s1.value, i.value) AS Value
    FROM @items i
    LEFT OUTER JOIN (
    	SELECT make, value FROM @supercede WHERE model IS NULL) AS s1
    	ON i.make = s1.make 
    LEFT OUTER JOIN (
    	SELECT model, value FROM @supercede WHERE make IS NULL) AS s2
    	ON i.model = s2.model 
    
    
    



    The execution plan for this one... 33% of the batch

     

     

     


    Jason Long
    • Proposed as answer by Stephanie Lv Wednesday, August 10, 2011 5:25 AM
    Tuesday, August 9, 2011 6:27 PM
  • Yes, that will work... and in reality, it's pretty much equivalent to the query with the OUTER APPLYs (if you take out the one I added that checks BOTH make and model)... Same basic query plan.

     


    --Brad (My Blog)
    Tuesday, August 9, 2011 6:32 PM
    Moderator
  • Thanks everyone for your responses.  Sorry for my slow return.  My RSS for this page didn't work and this project got pushed back for other items.

    Brad, I have been following your blog for some time.  While I don't have the SQL chops yet for many of your posts, this solution makes good sense to me.

    I do have a question about how to take it a little futher. Lets say the Superseeds table is a OnSale table.  How would I add a column to that current output that would indicate a sale item?

     

    Monday, August 15, 2011 10:57 AM