none
How do I build one column from two? RRS feed

  • Question

  • I have two tables.  One is the main permanent data, the other is a temporary deviation from the main data. 

    I have a left join query that puts the two together.  Normally the column for the deviation table is null.  So I would like a query that would make one column.  If the deviation table as a value that would be used otherwise the permanent data would be used.

    Here is a basic example of what I'm after

    TABLE P

    ITEM

    PRICE

    Red Apple

    $1.50

    Green Apple

    $1.00

    Pear

    $2.00

    Pineapple

    $4.00

    TABLE T

    ITEM

    PRICE

    Red Apple

    $0.25

    Pear

    $3.00

    Query Results I’m looking for

    ITEM

    PRICE

    Red Apple

    $0.25

    Green Apple

    $1.00

    Pear

    $3.00

    Pineapple

    $4.00

    Thank you for any and all help!

    Friday, December 17, 2010 4:22 PM

Answers

  • try this query please:

    ---------------------------------------------------------------------------
    ----------------------------------TABLE------------------------------------
    ---------------------------------------------------------------------------
    declare @P TABLE (ITEM varchar(50),PRICE float)
    declare @T TABLE (ITEM varchar(50),PRICE float)
    insert @P
    select 'Red Apple',1.50 UNION ALL
    select 'Green Apple',1.00 UNION ALL
    select 'Pear',2.00 UNION ALL
    select 'Pineapple',4.00 
    insert @T
    select 'Red Apple',0.25 UNION ALL
    select 'Pear',3.00
    ---------------------------------------------------------------------------
    ----------------------------------QUERY------------------------------------
    ---------------------------------------------------------------------------
    select P.ITEM,isnull(T.PRICE,P.PRICE) as PRICE from @P P
    Left join @T T on P.ITEM=T.ITEM
    ---------------------------------------------------------------------------
    ----------------------------------RSULTS------------------------------------
    ---------------------------------------------------------------------------
    --Red Apple 0.25
    --Green Apple 1
    --Pear 3
    --Pineapple 4

     

     

     


    Best regards
    • Edited by Badii Gharbi Friday, December 17, 2010 4:34 PM ISNULL
    • Marked as answer by DChiShaggy Friday, December 17, 2010 5:31 PM
    Friday, December 17, 2010 4:32 PM
  • Try

    declare @P TABLE (ITEM varchar(50),PRICE float)
    declare @T TABLE (ITEM varchar(50),PRICE float)
    insert @P
    select 'Red Apple',1.50 UNION ALL
    select 'Green Apple',1.00 UNION ALL
    select 'Pear',2.00 UNION ALL
    select 'Pineapple',4.00 
    insert @T
    select 'Red Apple',0.25 UNION ALL
    select 'Pear',3.00
    ---------------------------------------------------------------------------
    ----------------------------------QUERY------------------------------------
    ---------------------------------------------------------------------------
    select P.ITEM,isnull(T.PRICE,P.PRICE) as PRICE,
    CASE when T.PRICE IS NULL then NULL else 'On Sale' end as [On Sale]
     from @P P
    Left join @T T on P.ITEM=T.ITEM
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by DChiShaggy Friday, December 17, 2010 8:23 PM
    Friday, December 17, 2010 8:04 PM
    Moderator

All replies

  • Select P.ITEM, Coalesce(T.PRICE, P.PRICE) As PRICE
    From P
    Left Outer Join T On P.ITEM = T.ITEM;
    
    Tom
    Friday, December 17, 2010 4:26 PM
  • try this query please:

    ---------------------------------------------------------------------------
    ----------------------------------TABLE------------------------------------
    ---------------------------------------------------------------------------
    declare @P TABLE (ITEM varchar(50),PRICE float)
    declare @T TABLE (ITEM varchar(50),PRICE float)
    insert @P
    select 'Red Apple',1.50 UNION ALL
    select 'Green Apple',1.00 UNION ALL
    select 'Pear',2.00 UNION ALL
    select 'Pineapple',4.00 
    insert @T
    select 'Red Apple',0.25 UNION ALL
    select 'Pear',3.00
    ---------------------------------------------------------------------------
    ----------------------------------QUERY------------------------------------
    ---------------------------------------------------------------------------
    select P.ITEM,isnull(T.PRICE,P.PRICE) as PRICE from @P P
    Left join @T T on P.ITEM=T.ITEM
    ---------------------------------------------------------------------------
    ----------------------------------RSULTS------------------------------------
    ---------------------------------------------------------------------------
    --Red Apple 0.25
    --Green Apple 1
    --Pear 3
    --Pineapple 4

     

     

     


    Best regards
    • Edited by Badii Gharbi Friday, December 17, 2010 4:34 PM ISNULL
    • Marked as answer by DChiShaggy Friday, December 17, 2010 5:31 PM
    Friday, December 17, 2010 4:32 PM
  • Thank you both for your quick answers!  Both work but I am going with ISNULL because it is something I will remember.   

    Friday, December 17, 2010 5:36 PM
  • Now is it possible to add an other column to let me know I'm using the other table? Such as this?

    Red Apple     0.25   On Sale
    Green Apple  1.00   Null
    Pear             3.00   On Sale
    Pineapple      4.00   Null

     

    Friday, December 17, 2010 7:57 PM
  • Try

    declare @P TABLE (ITEM varchar(50),PRICE float)
    declare @T TABLE (ITEM varchar(50),PRICE float)
    insert @P
    select 'Red Apple',1.50 UNION ALL
    select 'Green Apple',1.00 UNION ALL
    select 'Pear',2.00 UNION ALL
    select 'Pineapple',4.00 
    insert @T
    select 'Red Apple',0.25 UNION ALL
    select 'Pear',3.00
    ---------------------------------------------------------------------------
    ----------------------------------QUERY------------------------------------
    ---------------------------------------------------------------------------
    select P.ITEM,isnull(T.PRICE,P.PRICE) as PRICE,
    CASE when T.PRICE IS NULL then NULL else 'On Sale' end as [On Sale]
     from @P P
    Left join @T T on P.ITEM=T.ITEM
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by DChiShaggy Friday, December 17, 2010 8:23 PM
    Friday, December 17, 2010 8:04 PM
    Moderator
  • Thank you very much.   I'm good with the basics, but I love to learn all these tricks that SQL has to offer. 

    Friday, December 17, 2010 8:26 PM