CODEWITHSUNDEEP
sql
Pavan Mirji
Pavan Mirji

Reputation: 3

Finding the rows from a table which matches the exact input

I have 2 tables

Table1
Col1             Col2
Committee name   Count of Employees

And 

Table2
Col1             Col2
Committee name   Employee name

I want to find the committee which matches the count of Employees and the given employee names

For e.g. i have these committees with given employees My input is EmpA, EmpB, EmpC and EmpD I am expecting only Cultural committee to be returned

Working Committee
EmpX
EmpY
EmpA
EmpB

Sports Committee
EmpM
EmpN
EmpA

Finance Committee
EmpA
EmpB
EmpC
EmpD
EmpE

Cultural committee
EmpA
EmpB
EmpC
EmpD

select table2.col1 from table1, table2 where table1.col2 = 4 and table2.col2 in ('EmpA', 'EmpB', 'EmpC', 'EmpD') and table1.col1 = table2.col1

With this query, it returns me all committees as EmpA is present in all of them. I want to do an exact match of Employees. Appreciate any inputs to solve my issue.

Thanks, Pavan

Upvotes: 0

Views: 58

Answers (4)

Popeye
Popeye

Reputation: 35900

I would prefer to use distinct count in HAVING clause for such a scenario to avoid the issue when same entry appears more than once in a table.

select t2.col2
from table2 t2
where t2.col2 in ('EmpA', 'EmpB', 'EmpC', 'EmpD') 
group by t2.col1
having count(distinct t2.col2) = 4;

Cheers!!

Upvotes: 0

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

This query uses counts that come from the data tables:

select t2.col1
from t2
where t2.col2 in ("EmpA", "EmpB", "EmpC", "EmpD")
group by t2.col1
having count(*) = (select t1.col2 from t1 where t1.col1 = t2.col1)

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

Here's your query.

select Col2 from table2 where Col1 in 
    (select col1 from table1 where Col2 =  
        (select count(1) ct from table2 where Col2 in ('EmpA', 'EmpB', 'EmpC', 'EmpD'))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269663

I think you want:

select t2.col1
from table2 t2
group by t2.col1
having sum(case when t2.col2 in ('EmpA', 'EmpB', 'EmpC', 'EmpD') then 1 else 0 end) = 4 and
       count(*) = 4;

table1 doesn't seem to be needed for this.

Note: That above assumes that there are no duplicates in table2.

Upvotes: 1

Related Questions