sql have function

Question

Hi I have following sql statement that gives me the the first_name and total number of jobs all together each employee has done but now I want to have the first_name and job_id of the jobs that an employee has being doing with another worker I have three tables altogether

Below is the code I used to get the first name and total number of jobs altogether

SELECT first_name, COUNT(link.job_id) 
FROM employee, job, link 
WHERE  job.job_id = link.job_id 
AND employee.employee_id = link.employee_id 
GROUP BY first_name 
HAVING (COUNT(link.job_id) > 1 ) ;

Can someone help please

barsju · Accepted Answer · 2012-04-04 08:52:53Z

2

How about this:

SELECT first_name, COUNT(link.job_id) 
FROM employee, job, link 
WHERE  job.job_id = link.job_id 
AND employee.employee_id = link.employee_id
AND job.job_id IN (SELECT job_id FROM link GROUP BY job_id HAVING COUNT(*) > 1) 
GROUP BY first_name 
HAVING (COUNT(link.job_id) > 1 ) ;

The subquery will get all jobs that involves more than one employee.

answered Apr 4, 2012 at 8:52

barsju

4,4561 gold badge23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql have function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related