I have two tables: Table A and Table B. Both of them have Emp-ID as key field. Table A has 10k records of unique EMP-IDs. Table B has 2k records of unique EMP-IDs. Some of the EMP-IDs in Table B also exist in Table A. I need to write a Sybase query listing all records of Table A and records from Table B whose EMP-ID does not exist in Table A. I need to select Table B records whose EMP_TYPE = 'Y'.

My below query is giving a syntax error:


select EMP_ID, EMP_TYPE from Table A where
EMP_ID not in (select EMP_ID from Table B where
EMP_TYPE='Y' and EMP_ID in
(select EMP_ID,EMP_TYPE from Table B
union all
select EMP_ID,EMP_TYPE from Table A))

Thanks in advance!