user1592147 user1592147 - 1 year ago 51
SQL Question

SQL query to calculate a flag based on the row value

TableA is a superset that has 1000 rows and TableB is a subset that has 500rows. Structure of both tables is same with ID and Empno. Write a SQL query that should result ID, Empno, flag(Y/N) shows Y if row in Table B is already present in TableA else flag shows N


id emp
1 a
2 b
3 c
4 d


id empno
1 a
2 b
3 f
4 g


id empno flag
1 a Y
2 b Y
3 f N
4 g N

select id, empno , ?case statement ??
from table b

Answer Source

Use an outer join and a check for NULL

select, a.empno, 
       case when is null then 'N' else 'Y' end as flag
from table_a a
   left join table_b b on a.empno = b.empno;

The above will show your for each row in table_a if it exists in table_b.

If you only want to see rows from table_b you could use something like this:

select id, empno, 
          when (select count(*) from table_a a where a.empno = b.empno) = 1 
               then 'Y' 
          else 'N'
from table_b b
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download