Update column based on table join

Transact-SQL
1

Hi,

I need help to find whether the record is there in both the table or just one table

SELECT a.producttype, b.amount, Status From Table1 a Left outer join Table2 b on a.numberid = b.numberid

the column Status = both, when there is matching record in both the table
Status = table1 when there is record only in table1
Status = table2 when there is record only in table2

Any suggestion will be of great help

thanks in advance.

2

this is just a hint .. hope it helps .. please let me know if u want detailed explanation

FULL OUTER JOIN

when table2 IS NULL then Status = Table1

when table1 IS NULL then Status = Table2

3 
SELECT 
    COALESCE(a.producttype, b.producttype) AS producttype, 
    COALESCE(a.amount, b.amount) AS amount, 
    CASE WHEN a.Status IS NULL THEN b.Status
         WHEN b.Status IS NULL THEN a.Status
         ELSE 'Both' END AS Status 
FROM Table1 a 
FULL OUTER JOIN Table2 b ON a.numberid = b.numberid