Search code examples
mysqlsqlhaving-clause

sql have function

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

Solution

  • 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.