Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2013
    Posts
    41

    Unanswered: Sub Query : Improper Result !

    Hello,

    I'm executing following query:

    List the empno, ename, and dept name for any employees that work in the same department as CLARK.
    Use a subquery in the where clause to determine which department CLARK works in.

    Here is my query:

    Code:
    SELECT e.empno, e.ename, d.dname
    FROM EMP e , DEPT d
    WHERE d.DEPTNO IN (SELECT DEPTNO
                     FROM EMP 
                     WHERE EMPNO = 7782)
    AND d.DEPTNO = e.DEPTNO;

    But the problem with above query is that it is listing CLARK details as well. How can I omit CLARK details as I just want to display others who work with CLARK ?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd bet that Clark works in the same department as Clark. Why shouldn't your query list Clark?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2013
    Posts
    41
    Yeah, that make sense. But I'm wondering is there any way to omit one result?

    Quote Originally Posted by Pat Phelan View Post
    I'd bet that Clark works in the same department as Clark. Why shouldn't your query list Clark?

    -PatP

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Certainly, just throw a "not equals" test in the WHERE clause.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Two examples.
    (Not tested.)

    Example 1:
    Code:
    SELECT e2.empno, e2.ename, d.dname
     FROM  EMP  e1
     INNER JOIN
           DEPT d
      ON   d.deptno = e1.deptno
     INNER JOIN
           EMP  e2
      ON   e2.deptno = d.deptno
       AND e2.empno <> e1.empno
     WHERE e1.empno =  7782
    ;

    Example 2:
    Code:
    SELECT e.empno, e.ename, d.dname
     FROM  (SELECT empno , ename
                 , deptno
                 , MAX(CASE empno
                       WHEN 7782 THEN
                            deptno
                       END  ) OVER() AS max_dept
             FROM  emp
           ) AS e
     INNER JOIN
           dept AS d
      ON   d.deptno = e.deptno
     WHERE e.deptno = e.max_dept
       AND e.empno <> 7782
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •