Suppose i have a table empgroupinfo and i want to fetch the employeeid who come in exact this two groupId 500 and 501 (will come dynamically) only, should not come in more or less number of group, where empid != 102 which is in 500 groupid.
I have tried following query:
select empid from empgroupinfo
where empgroupid in(500,501) and empid != 102
group by empid having count(empid) = 2
But this above query also returns the empId that are in other groups.
I want to fetch the empid for the case when employees are in exactly these two groupids (500 and 501) only and empid != 102.
Your WHERE clause selects rows where empgroupid is either 500 or 501, not empids where all the empgroupids form the array [500, 501].
You could use an ARRAY_AGG in the HAVING clause:
SELECT empid
FROM empgroupinfo
GROUP BY empid
-- ORDER BY clause here is important, as array equality checks elements position by position, not just 'same elements as'
HAVING ARRAY_AGG(DISTINCT empgroupid ORDER BY empgroupid) = ARRAY[500, 501]
Depending on where the [500, 501] array comes from, you may not know whether it itself is sorted or not. In that case a "contains AND is contained by" (operators @> and <@) should work too.
#= CREATE TABLE empgroupinfo (empid int, empgroupid int);
CREATE TABLE
Time: 10,765 ms
#= INSERT INTO empgroupinfo VALUES (1, 500), (1, 501), (2, 500), (2, 501), (2, 502);
INSERT 0 5
Time: 1,451 ms
#= SELECT empid
FROM empgroupinfo
GROUP BY empid
HAVING ARRAY_AGG(empgroupid ORDER BY empgroupid) = ARRAY[500, 501];
┌───────┐
│ empid │
├───────┤
│ 1 │
└───────┘
(1 row)
Time: 0,468 ms