SQL query for matching multiple values in the same column

Question

I have a table in MySQL as follows.

Id   Designation           Years          Employee
1    Soft.Egr            2000-2005           A
2    Soft.Egr            2000-2005           B
3    Soft.Egr            2000-2005           C
4    Sr.Soft.Egr         2005-2010           A
5    Sr.Soft.Egr         2005-2010           B
6    Pro.Mgr             2010-2012           A

I need to get the Employees who worked as Soft.Egr and Sr.Soft.Egr and Pro.Mgr. It is not possible to use IN or Multiple ANDs in the query. How to do this??

You have to use self join to get you desired result. Refer tutorialspoint.com/sql/sql-self-joins.htm — Meherzad
– Meherzad, Commented May 24, 2013 at 7:28

user359040 · Accepted Answer · 2013-05-24 07:32:12Z

47

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

answered May 24, 2013 at 7:32

user359040

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

Comments

Lukas Eder · Accepted Answer · 2013-05-24 07:45:20Z

11

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

edited May 24, 2013 at 7:45

answered May 24, 2013 at 7:37

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

2 Comments

ninesided Over a year ago

we don't know for sure that these are all the possible designations, these are just the ones in the example - interesting solution nonetheless!

Lukas Eder Over a year ago

@ninesided: I know, you're probably right. But I have a gut feeling (since this is an academic question), that this could be about relational division. And if it's not, it's still good to raise relational division awareness on Stack Overflow :-)

ninesided · Accepted Answer · 2013-05-24 07:34:56Z

2

If you need to get additional information back about each of the roles (like the dates) then joining back to your original table for each of the additional designations is a possible solution:

SELECT t.Employee, t.Designation, t.Years, t1.Designation, t1.Years, t2.Designation, t2.Years
FROM Table t
INNER JOIN t2 ON (t2.Employee = t.Employee AND t2.Designation = 'Sr.Soft.Egr')
INNER JOIN t3 ON (t3.Employee = t.Employee AND t3.Designation = 'Soft.Egr')
WHERE t.Designation = 'Pro.Mgr';

answered May 24, 2013 at 7:34

ninesided

23.3k17 gold badges88 silver badges109 bronze badges

Comments

Sida Zhou · Accepted Answer · 2015-10-13 23:10:11Z

2

Why not the following (for postgresql)?

SELECT employee FROM Employees WHERE Designation ='Sr.Soft.Egr'
INTERSECT 
SELECT employee FROM Employees WHERE Designation ='Soft.Egr'
INTERSECT 
SELECT employee FROM Employees WHERE Designation ='Pro.Mgr'

Link to SQLfiddle

I know this might not optimized, but I find this much much easier to understand and modify.

edited Oct 13, 2015 at 23:10

answered Oct 13, 2015 at 23:04

Sida Zhou

3,7653 gold badges38 silver badges51 bronze badges

Comments

alex.pulver · Accepted Answer · 2018-04-26 07:14:11Z

0

Try this query:

SELECT DISTINCT t1.employee, 
                t1.designation 
FROM tempEmployees t1, tempEmployees t2, tempEmployees t3 
WHERE t1.employee = t2.employee AND
      t2.employee = t3.employee AND 
      t3.employee = t1.employee AND
      t1.designation != t2.designation AND 
      t2.designation != t3.designation AND 
      t3.designation != t1.designation

edited Apr 26, 2018 at 7:14

alex.pulver

2,1232 gold badges34 silver badges33 bronze badges

answered Apr 26, 2018 at 6:07

Nageswar Rao

1

Collectives™ on Stack Overflow

SQL query for matching multiple values in the same column

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related