How to use sub query to list from a count statement?

Question

We have some employees in TOTEMP table who has duplicate CIVILID and now i want to find their names matching from EMP table

I tried the following queries

SELECT P.FIRSTNAME,ID
FROM EMP p
where  exists (SELECT CIVILID, COUNT (CIVILID)
               FROM TOTEMP AS D
               group by CIVILID
               HAVING (COUNT (CIVILID) >1) ) 

Selects all the records from the EMP table

2 ) I tried an IN statement

SELECT P.FIRSTNAME,p.ID
FROM EMP p ,UDFEMP  k
where  p.ID in (SELECT CIVILID ,COUNT (CIVILID)
                FROM TOTEMP AS D
                group by CIVILID
                HAVING (COUNT (CIVILID) >1) ) 

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Regards

Answer 1

SELECT p.FIRSTNAME,p.ID,d.CIVILID
    FROM EMP p inner join TOTEMP d
    on p.id = d.id
    where  d.CIVILID in (select CIVILID from TOTEMP
                        group by CIVILID having count(ID)>1 );

Hope it helps!