CODEWITHSUNDEEP
sqlsql-server
Pavel Kašelják
Pavel Kašelják

Reputation: 351

SQL multiple active jobs query

This is the part of the db I am working with.

So I'm trying to put together a query that will show me employees who have worked on more than one active job. So far I have managed to write a query that will get me a list of people who are working on one or more active jobs.

SELECT 
   cont_employee.*,contract.*
FROM 
   cont_employee
JOIN 
   contract ON contract.emp_no = cont_employee.emp_no
WHERE 
   is_active = 1

I'm not really sure how to progress from this point.

Upvotes: 0

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You need some sort of aggregation. Here is my recommendation, assuming that is_active is in the contractor table and that is what you really mean in your query:

SELECT ce.emp_no
FROM cont_employee ce JOIN
     contractor c
     ON c.emp_no = ce.emp_no
WHERE c.is_active = 1
GROUP BY ce.emp_no
HAVING COUNT(*) > 1;

Upvotes: 1

Related Questions