CODEWITHSUNDEEP
sql
Jika
Jika

Reputation: 1584

How to solve this query in SQL?

I have the following two relations:

EMP is a relation of the employees with their number ENO, their names ENAME, their job titles JOB, the dates when they get hired, their salary SAL, and the department number they are working on DNO (forgein key which references DNO in DEPT).

DEPT is a relation of the department with the number of each department DNO, the name of the department DNAME, the director of the department DIR (forgein key which references ENO).

My question is:

Find the names of the employees that have the same job and the same director as 'Joe'.

My attempt was:

SELECT ENAME 
FROM   EMP, DEPT
WHERE  EMP.DNO = DEPT.DNO
AND    (DIR, JOB) IN (
                      SELECT DIR, JOB
                      FROM   EMP, DEPT
                      WHERE  ENAME = 'Joe'
                      AND    EMP.DEPT = DEPT.DNO
                      )
AND  ENO NOT IN (
                 SELECT ENO
                 FROM   EMP, DEPT
                 WHERE  ENAME = 'Joe'
                 AND    EMP.DEPT = DEPT.DNO
                )

I found the solution of this problem but I couldn't agree of it.

This is what I found:

SELECT ENAME
FROM   EMP, DEPT
WHERE  ENAME <> 'Joe'
AND    EMP.DNO = DEPT.DNO
AND    (DIR, JOB) = (
                     SELECT DIR, JOB
                     FROM   EMP, DEPT
                     WHERE  ENAME = 'Joe'
                     AND    EMP.DEPT = DEPT.DNO
                     )

The thing is, we have to not consider 'Joe' in the result. But which 'Joe'?

Upvotes: 0

Views: 1795

Answers (3)

Andrew
Andrew

Reputation: 4624

The comma style of join you are using has been obsolete for a long time. I think the below is what you're after. The idea is to join a table to its self. This is done by giving the table aliases- source and twin here.

SELECT twin.ENAME
FROM EMP AS source
JOIN EMP AS twin ON twin.DNO = source.DNO AND twin.JOB = source.JOB
WHERE source.ENAME = 'Joe' AND source.ENO <> target.ENO

Upvotes: 1

spencer7593
spencer7593

Reputation: 108370

It looks like there's a potential for a "director" to head multiple departments. At least, the information model doesn't seem to be anything to restrict that (i.e. no unique constraint on DIR)

Presumably, we identify employee 'Joe' by finding the tuples(s) in EMP with ENAME attribute equal to 'Joe'.

And presumably, we would identify Joe's "director" by getting the value of the DIR attribute from the DEPT relation.

If we wanted employees in the "same department" as Joe, we could just use the value of the DNO attribute,... but the requirement says "same director". So, just in case the same director heads multiple departments, we'll get all the departments headed by that director.

Then, it's a simple matter of getting all of the employees in those departments, and check for a "job" that matches Joe's "job".

SELECT e.ENAME
  FROM EMP j
  JOIN DEPT i
    ON i.DNO = j.DNO
  JOIN DEPT d
    ON d.DIR = i.DIR
  JOIN EMP e
    ON e.DNO = d.DNO
   AND e.JOB = j.JOB
 WHERE j.ENAME = 'Joe'

Again, if we wanted only the employees in the "same department" as Joe, we could dispense with one of those references to DEPT. The result from this would be different, if Joe's director heads another department, and there's an employee in that other department has the same job... that employee would be excluded from this query:

 SELECT e.ENAME
   FROM EMP j
   JOIN DEPT i
     ON i.DNO = j.DNO
-- JOIN DEPT d
--    ON d.DIR = i.DIR
  JOIN EMP e
--  ON e.DNO = d.DNO
    ON e.DNO = i.DNO
   AND e.JOB = j.JOB
 WHERE j.ENAME = 'Joe'

If there's a requirement to exclude Joe from the resultset, then we could add another predicate to the WHERE clause. If we don't assume that ENAME can't have a NULL value...

   AND ( e.ENAME IS NULL OR e.ENAME <> 'Joe')

Upvotes: 1

CindyH
CindyH

Reputation: 3026

You're correct in that the second solution is wrong. If there are two 'Joe's it won't work right. That's why you should exclude based on the unique ENO instead of the non-unique name. The first query won't work for the same reason. In order to be certain, you can't select either just by names or titles or departments, because those can be duplicate. We have three Chris programmers in our department.

Also, that join syntax is obsolete because it can cause confusion to the database in certain circumstances. Please see http://www.w3schools.com/sql/sql_join_inner.asp for an explanation of the current syntax.

Upvotes: 1

Related Questions