user1592147 user1592147 - 4 months ago 8
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

TABLE A

id emp
1 a
2 b
3 c
4 d


TABLE B

id empno
1 a
2 b
3 f
4 g


Resultset

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


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

Answer

Use an outer join and a check for NULL

select a.id, a.empno, 
       case when b.id 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, 
       case 
          when (select count(*) from table_a a where a.empno = b.empno) = 1 
               then 'Y' 
          else 'N'
       end
from table_b b
Comments