Question

How would you rewrite this query using a method other than Union, the method that is currently being used. I need this same query rewritten, and if possible in a syntax that will work with Microsoft Access, as that is the platform that I have to test it.

Here is the original query to be rewritten using a query other than Union:

(SELECT EmployeeName

FROM Employee_t E1, EmployeeSkills_t ES1, Skill_t S1

WHERE E1.EmployeeID = ES1.EmployeeID AND ES1.SkillID = S1.SkillID

              AND S1.SkillDescription = ’12in Band Saw’)

MINUS

(SELECT EmployeeName

FROM Employee_t E2, EmplpoyeeSkills_t ES2, Skills_t S2

WHERE E2.EmployeeID = ES2.EmployeeID AND ES2.SkillID = S2.SkillID

              AND S2.SkillDescription = ‘Router’)

So effectively in the end this needs to be written without MINUS, in any other query form. I am trying to understand how to convert from Union as that is the only one that I am currently good in and I need to learn other ways of writing queries.

Answer 1

you can achieve the same result using a subquery

SELECT DISTINCT E1.EmployeeName
FROM  Employee_t E1, EmployeeSkills_t ES1, Skill_t S1
WHERE E1.EmployeeID = ES1.EmployeeID AND ES1.SkillID = S1.SkillID AND 
S1.SkillDescription = ’12in Band Saw’

NOT IN (SELECT EmployeeName FROM Employee_t E2, EmplpoyeeSkills_t ES2, Skills_t S2 
WHERE E2.EmployeeID = ES2.EmployeeID AND ES2.SkillID = S2.SkillID AND S2.SkillDescription = ‘Router’)

OR

SELECT E.EmployeeName 
FROM Employee_t E,Skills_t s
JOIN EmplpoyeeSkills_t ES ON (E.EmployeeID = ES.EmployeeID)
where S1.SkillDescription = ’12in Band Saw’