Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    51

    Unanswered: Problem in union

    I want the query to have the same output in both the cases

    SQL> select * from list;



    EMPNO EMPNAME

    ----- ----------

    1001 aaa

    1002 bbb

    1003 ccc

    1004 ddd

    1005 eee

    1006 hhh

    1007 fff



    7 rows selected.



    SQL> select * from score;



    EMPNO DET1 DET2 DET3

    ----- ---------- ---------- ----------

    1001 23 34

    1002 12 35 47

    1003 40

    1004 12 34 46

    1005 33 22

    1006 44 33 22

    1004 12 34 46

    1005 44 33 22

    44 33 22

    20 34 26



    10 rows selected.



    This is my join condition:

    SQL> select list.empno,score.det1 from list,score where list.empno=score.empno(+);



    EMPNO DET1

    ----- ----------

    1001 23

    1002 12

    1003 40

    1004 12

    1004 12

    1005

    1005 44

    1006 44

    1007



    9 rows selected.



    I want the same result without using the join….

    I tried the below query and it is not fetching any duplicate record as I have used union..so what do I do..???



    Expanded query:



    SQL> ed

    Wrote file afiedt.buf



    1 select list.empno,score.det1 from list,score where list.empno=score.empno

    2 union

    3 select list.empno,NULL from list,score where list.empno not in(select

    4* score.empno from score where score.empno is not null)

    SQL> /



    EMPNO DET1

    ----- ----------

    1001 23

    1002 12

    1003 40

    1004 12

    1005

    1005 44

    1006 44

    1007



    8 rows selected.


    Please help?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Use UNION ALL instead of UNION. This might help.

Posting Permissions

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