user3099298 user3099298 - 4 months ago 10
SQL Question

SQL Join did not work (zero records return)?

I found this question from a tutorial,

Emp( eid: integer, ename: string, age: integer, salary: real)
Works( eid: integer, did: string, pct-time: integer)
Dept( did: string, budget: real, managerid: integer)


As that question Employee can work many departments. Work table used to map employees and departments. The pct_time field of the Works relation shows the percentage of time that a given employee works in a given department.

Then it ask to print names and ages of each employee who works in both the Hardware and Software department.

For that I have written following SQL but it did not work? can someone please explain the issue.

SELECT Emp.ename, Emp.age
FROM Emp
JOIN Works ON Works.eid = Emp.eid
WHERE Works.did = 'Hardware' AND Works.did = 'Software'

Answer

In a single record the Works.did can't be Hardware and Software at once. So you need to group your data, filter on the Works.did you look for and then take only the groups having both Works.did

SELECT Emp.ename, Emp.age
FROM Emp
JOIN Works ON Works.eid = Emp.eid
WHERE Works.did IN ('Hardware', 'Software')
GROUP BY Emp.ename, Emp.age
HAVING count(Works.did) = 2