locked
sql problem/stored procedure RRS feed

  • Question

  • User932259438 posted

    hello,

    table 1:
    Id, CategoryId, Amount

    table 2:
    id, CategoryId, Amount

    Question:

    How Can I used amount from table 2 if exists, if not exists used from table 1 because in table 1 every time exists?

    Monday, November 28, 2016 12:26 AM

Answers

  • User-158764254 posted

    I'd left outer join the two tables together.

    This assumes that both Id and CategoryId need to match between the two tables for them to represent the same item.

    SELECT table1.id,
           table1.categoryid,
           Coalesce(table2.amount, table1.amount) AS Amount
    FROM   table1
           LEFT OUTER JOIN table2
                        ON table1.id = table2.id
                           AND table1.categoryid = table2.categoryid  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 28, 2016 1:27 AM
  • User364663285 posted

    Try

    select CategoryId, Amount
    from (select id, CategoryId, Amount from [table 2]
    union 
    select id, CategoryId, Amount from [table 1]) q
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 28, 2016 7:06 AM

All replies

  • User-158764254 posted

    I'd left outer join the two tables together.

    This assumes that both Id and CategoryId need to match between the two tables for them to represent the same item.

    SELECT table1.id,
           table1.categoryid,
           Coalesce(table2.amount, table1.amount) AS Amount
    FROM   table1
           LEFT OUTER JOIN table2
                        ON table1.id = table2.id
                           AND table1.categoryid = table2.categoryid  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 28, 2016 1:27 AM
  • User364663285 posted

    Try

    select CategoryId, Amount
    from (select id, CategoryId, Amount from [table 2]
    union 
    select id, CategoryId, Amount from [table 1]) q
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 28, 2016 7:06 AM