Display all employees working on all of the projects?

Question

table 1 includes employee name and their initials table 2 includes projectnumber and pmember(initials as well)

we only have to display the name of the employees so i have to use a WHERE clause, which i can't construct :( here's what i've done so far. it only returns 1 employee (correct answer is 2 employees)

select t1.name
from t1
where t1.initials IN (select t2.pmember
                     from t2
                     having count(t2.projectnumber) > 1)

thanks in advance!

why do you use `having`? If you use it, you must have a group by — agim, Mar 02 '14 at 11:18
That is your Query? Which employee you wants to print workon? — Grijesh Chauhan, Mar 02 '14 at 11:19
are you looking for employees who have more than project ? Or all employees what have a minimum of 1 project ? — Armand DOHM, Mar 02 '14 at 11:23
hi. i am looking for employees working on all of the projects. the whole database only has 2 projects, so i used count(t2.projectnumber) > 1 — user3363032, Mar 02 '14 at 11:30
@user3363032 then you should use `=2` and eventually tell us why you do that.. it would make the query readable and we would know what you mean.. My query should work even with three projects in the database ;) — agim, Mar 02 '14 at 11:33
is JOIN really necessary? because we were taught that we will just use join if we need to return something from 2 or more tables. — user3363032, Mar 02 '14 at 11:37
Yes it is necessary.. How else will you make sure, the members work on all projects? To return does not necessarily mean do display.. — agim, Mar 02 '14 at 11:38
Please remember to mark one of the answers as "accepted" if they've helped you. — KenD, Mar 02 '14 at 13:32

kiks73 · Answer 1 · 2014-03-02T11:49:24.013

0

To show all emp that works on all projects, you need a distinct count like this, I think:

SELECT NAME FROM T1 WHERE INITIALS IN ( 
         SELECT PMEMBER FROM T2 HAVING DISTINCT COUNT(PROJECTNUMBER) = (
             SELECT DISTINCT COUNT(PROJECTNUMBER) FROM T2)
         GROUP BY PMEMBER)

edited Mar 02 '14 at 11:49

answered Mar 02 '14 at 11:21

kiks73

3,718
3
25
52

2

If he wants to show the members who work on all projects? you sure?? – agim Mar 02 '14 at 11:21
i only need to return the name of the employee so i won't have to use JOIN clauses, or is that right? – user3363032 Mar 02 '14 at 11:26

score 0 · Answer 2 · answered Mar 02 '14 at 11:21

0

select t1.name
from t1
left join t2 on t1.initials = t2.pmember
group by t1.name
having sum(t2.projectnumber is null) = 0

answered Mar 02 '14 at 11:21

juergen d

201,996
37
293
362

score 0 · Answer 3 · answered Mar 02 '14 at 11:28

0

This query should give you the names of the members working on all projects:

SELECT T1.NAME
  FROM T1
  JOIN T2 ON T1.INITIALS = T2.PMEMBER
 GROUP BY T1.NAME
HAVING COUNT(T2.PROJECTNUMBER) = (SELECT COUNT(1) FROM T2 T2ALIAS)

answered Mar 02 '14 at 11:28

agim

1,841
12
19

score 0 · Answer 4 · answered Mar 02 '14 at 11:30

0

Maybe this query helps you:

SELECT T1.NAME 
FROM T1 
INNER JOIN T2 ON T1.INITIALS = T2.PMEMBER 
HAVING COUNT(T2.PROJECTNUMBER) > 1

answered Mar 02 '14 at 11:30

iubeda

1
1

Display all employees working on all of the projects?

4 Answers4