CODEWITHSUNDEEP
sqlcountsubquerysql-server-2014exists
aryan
aryan

Reputation: 49

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

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

enter image description here

Upvotes: 1

Views: 580

Answers (2)

Harshil Doshi
Harshil Doshi

Reputation: 3592

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!

Upvotes: 1

Mureinik
Mureinik

Reputation: 311998

This can be done both with the exists and the in operators, but both your queries have issues.

The exists variant is missing a relation between p and d:

SELECT p.firstname, p.id
FROM   emp p
WHERE  EXISTS (SELECT   *
               FROM     totemp d
               WHERE    p.id = d.civilid -- Here!
               GROUP BY civilid
               HAVING   COUNT(civilid) > 1)

The in variant needs to just select the id in the inner query, not multiple columns (and doesn't need to cross join with udfemp):

SELECT p.firstname, p.id
FROM   emp p
where  p.id in (SELECT   civilid
                FROM     totemp
                GROUP BY civilid
                HAVING   COUNT(civilid) > 1)

Upvotes: 0

Related Questions