SQL query for matching multiple values in the same column

One way:

select Employee
from job_history
where Designation in ('Soft.Egr','Sr.Soft.Egr','Pro.Mgr')
group by Employee
having count(distinct Designation) = 3

What you might actually be looking for is relational division, even if your exercise requirements forbid using AND (for whatever reason?). This is tricky, but possible to express correctly in SQL.

Relational division in prosa means: Find those employees who have a record in the employees table for all existing designations. Or in SQL:

SELECT DISTINCT E1.Employee FROM Employees E1
WHERE NOT EXISTS (
    SELECT 1 FROM Employees E2
    WHERE NOT EXISTS (
        SELECT 1 FROM Employees E3
        WHERE E3.Employee = E1.Employee
        AND E3.Designation = E2.Designation
    )
)

To see the above query in action, consider this SQLFiddle

A good resource explaining relational division can be found here: http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division

Tags:

Mysql