CODEWITHSUNDEEP
mysqlsql
user2922456
user2922456

Reputation: 391

Subselect with count, SQL query

Hi I need to select the empno and the salary of all the female workers that in thier department are at least two male with the same salary like the female..

This is the tables

EMP:
empno|ename|deptno|sal|gender   
DEPT:
deptno|dname

This is my code, in some reason this dosent give the needed result

SELECT *
FROM EMP E
WHERE E.GENDER = 'F' AND 2 <= (SELECT COUNT(*)
                               FROM EMP E2
                               WHERE E2.GENDER = 'M' AND
                               E2.SAL = E.SAL
                               AND E.DEPTNO = E2.DEPTNO);

Upvotes: 4

Views: 53

Answers (2)

juergen d
juergen d

Reputation: 204854

select e1.* 
from emp e1
join
(
    select sal, deptno
    from emp
    group by sal, deptno
    having count(distinct gender) = 2
    and sum(gender = 'M') >= 2
) e2 on e1.sal = e2.sal and e1.deptno = e2.deptno
where e1.gender = 'F'

SQLFiddle demo

Upvotes: 1

sgeddes
sgeddes

Reputation: 62851

There are several ways to do this. Here's one option using exists:

select empno, sal
from emp e
where gender = 'F'
and exists (
  select 1
  from emp e2 
  where e2.gender = 'M' 
    and e.sal = e2.sal and e.deptno = e2.deptno
  having count(*) > 1)

Upvotes: 0

Related Questions