SQL IF Statement or Flag using 2 Tables

Question

I have 2 tables (1 main table and 1 supplementary table) with ID #'s on each table. Some ID #'s appear on both tables and some don't. I need to flag/new field (named Both) which shows if the ID # appear on the supplementary table and the main table.

I am new to SQL and know of IF statements but haven't had much success using with it using more than 1 table.

Main Table

ID#   | Fruit                       
1     | apple                        
2     | peach                       
3     | orange
4     | pear

Supplementary Table

ID#    | Fruit
 3     | orange
 4     | pear

Any help would be appreciated! -Newb

I am using SQL Server 2014 Management Studio. The expect outcome is a new field that simple has a Y or No (1 or 0) titled 'Both' so that when I look up the specific ID #, I know it's on both tables. — ddot201, Jan 17 '17 at 15:44

score 0 · Accepted Answer · answered Jan 17 '17 at 15:24

You could use a left join and then a case expression on the ID of the supplementary table:

SELECT    m.id, 
          m.fruit,
          CASE WHEN s.id IS NOT NULL THEN 'yes' ELSE 'no' END AS both
FROM      main m
LEFT JOIN supplementary s ON m.id = s.id

SQL IF Statement or Flag using 2 Tables

1 Answers1