CODEWITHSUNDEEP
mysql
newbie
newbie

Reputation: 14950

MYSQL IN substitute

Good day

I am trying to query the names of employees who work on every project. My code is as follows:

SELECT CONCAT (fname,' ', minit,'. ' , lname) AS NAME
FROM employee a, works_on b, project c
WHERE pno IN (Select pnumber      //WHAT COULD I SUBSTITUTE W/ IN
              FROM projet) 
     AND a.ssn = b.essn
     AND b.pno = c.pnumber

The problem with IN is that it is that the values inside are like evaluated as 'OR'... what is the equivalent of IN that makes the value of my subquery evaluated like 'AND'

Thank you in advance.

EDIT: As reqeusted..

enter image description here

Upvotes: 2

Views: 237

Answers (3)

ADW
ADW

Reputation: 4080

select CONCAT (fname,' ', minit,'. ' , lname) AS NAME 
from employee 
left join works_on 
on works_on.essn=employee.ssn 
group by employee.ssn 
having count(works_on.essn) =  (select count(*) from project);

Upvotes: 2

Michael Buen
Michael Buen

Reputation: 39393

Simplified example:

create table emp_work_on
(
emp_name varchar(50),
work_on varchar(30)
);


create table works
(
work_on varchar(30)
);

insert into works(work_on) values('apple'),('microsoft'),('google'),('facebook')



insert into emp_work_on values
('john','apple'),('john','microsoft'),('john','google'),('john','facebook'),
('paul','microsoft'),('paul','google'),
('george','apple'),('george','microsoft'),('george','google'),('george','facebook'),
('ringo','apple'),('ringo','facebook');


select e.emp_name
from works w
left join emp_work_on e on e.work_on = w.work_on
group by e.emp_name
having count(e.work_on) = (select count(*) from works)
order by e.emp_name

Output:

emp_name 
----------
 george
 john
(2 rows)

On your table structure, you could use this:

SELECT * FROM employee 
WHERE ssn IN
(
    SELECT w.essn
    FROM project c
    LEFT JOIN works_on w ON w.pno = c.pnumber
    GROUP BY w.essn
    HAVING COUNT(w.pno) = (SELECT COUNT(*) FROM project)
)

Hmm.. but I think this could be the simplest, granting there's no repeating pno on employee's works_on, i.e. there's no pno in works_on that doesn't exists on project, i.e. referential integrity is maintained

SELECT * FROM employee 
WHERE ssn IN
(
    SELECT essn
    FROM works_on
    GROUP BY essn
    HAVING COUNT(pno) = (SELECT COUNT(*) FROM project)
)

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425431

SELECT  *
FROM    employee e
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    employee ei
        CROSS JOIN
                project p
        LEFT JOIN
                works_on wo
        ON      wo.pno = p.pnumber
                AND wo.essn = ei.ssn
        WHERE   ei.ssn = e.ssn
        )

Upvotes: 4

Related Questions