CODEWITHSUNDEEP
sql
KevinCameron1337
KevinCameron1337

Reputation: 517

SQL queries on RELATION TABLE

Employee can work in many departments, and each department can have many employees. Hence a relation table is needed

TABLES:
EMP(eid,ename,eage,esalary)
Works(eid,did,pct_time)
Dept(did,dname,budget, managerid)

Find all the employees that work in both software and hardware:

SELECT e.ename FROM EMP e, Works w, Dept d 
WHERE e.eid = w.eid AND w.did = d.did AND d.did = 
(SELECT did FROM dept WHERE dname = 'Hardware' OR 'Software')

As Per Jon: 

SELECT e.ename FROM EMP e OR Works w OR Dept d 
WHERE e.eid = w.eid AND w.did = d.did AND d.did = 
(SELECT did FROM dept WHERE dname = 'Hardware' OR 'Software')

And I just can't figure out how to make sure there are two entries in Works for both hardware and software under the same eid.

Upvotes: 1

Views: 12899

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 753595

Here's an alternative formulation of the query:

SELECT e.*
  FROM emp AS e
  JOIN (SELECT w.eid
          FROM works AS w JOIN dept AS d
            ON d.did = w.did AND d.dname = 'Software') AS s
    ON s.eid = e.eid
  JOIN (SELECT w.eid
          FROM works AS w JOIN dept AS d
            ON d.did = w.did AND d.dname = 'Hardware') AS h
    ON h.eid = e.eid

This has two symmetric sub-queries, one generating the employee IDs for the Software department, one generating employee IDs for the Hardware department. The selected employees are those whose ID is listed in both departments (because they're all inner joins).

Upvotes: 1

Zane Bien
Zane Bien

Reputation: 23125

Here are the steps on how you can tackle the problem:

  • First, get all rows in the works table where the department is EITHER hardware or software
  • Then, for any employee, if two rows exist, that means he/she works for BOTH departments.
  • For any employee, if less than two rows exist, that means he/she only works for one of the two, or none at all.
  • We need to retrieve the employees that have exactly two rows.

So what we can first do is select from the works table, join it against the dept table, and filter out any departments that are not hardware or software:

SELECT a.*
FROM works a
INNER JOIN dept b ON a.did = b.did
WHERE b.dname IN ('Hardware','Software')

Then what we want to do is group by each employee, which will give us access to aggregate information such as the count of rows per employee, or sum/max/min/avg/etc. of other columns. We must work with count:

SELECT a.eid
FROM works a
INNER JOIN dept b ON a.did = b.did
WHERE b.dname IN ('Hardware','Software')
GROUP BY a.eid
HAVING COUNT(1) = 2

This gives us only the eids that have exactly two rows (i.e. works for BOTH software AND hardware departments).

But the eids aren't enough. We want the entire information about the employee, so we must wrap that entire query and join it against the employees table to get the rest of their information:

SELECT a.*
FROM emp a
INNER JOIN 
(
    SELECT a.eid
    FROM works a
    INNER JOIN dept b ON a.did = b.did
    WHERE b.dname IN ('Hardware','Software')
    GROUP BY a.eid
    HAVING COUNT(1) = 2
) b ON a.eid = b.eid

And there you have it: employees who work in both software AND hardware departments. You can execute each of the queries above individually to see the intermediate steps on how we arrive at our final solution.

Upvotes: 1

Phan
Phan

Reputation: 114

select e.name
  from emp e, works h_w, dept h_d, works s_w, dept s_d
 where e.eid = h_w.eid
   and e.eid = s_w.eid
   and h_w.did = h_d.did
   and h_d.dname = 'Hardware'
   and s_w.did = s_d.did
   and s_d.dname = 'Software'

Upvotes: 2

Related Questions