Results 1 to 3 of 3

Thread: orqcle query

  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Question Unanswered: orqcle query

    Hi All,

    I have two tables :


    Item :

    Item_no Description
    -------- -----------
    1 Pen
    2 Paper
    3 Eraser
    .... ........

    Transaction :

    Tran_no Item_no Qty
    -------- -------- ----
    a1 2 100
    a2 3 40
    a3 50
    a4 1 30

    I want to display

    Tran_No Item_no Description
    --------- --------- -------------
    for all transcations even for those where item_no is null.

    What will be join condition ?

    Thanks

    Jayanta

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: orqcle query

    That is an "outer join". There are 2 ways to write this in Oracle:

    1) Traditional Oracle way:

    select t.tran_no, t.item_no, i.description
    from transactions t, items i
    where t.item_no = i.item_no (+);

    2) Standard ANSI way - available in Oracle as of 9i:

    select t.tran_no, t.item_no, i.description
    from transactions t
    left outer join items i
    on t.item_no = i.item_no;

  3. #3
    Join Date
    Oct 2003
    Posts
    2
    SELECT * FROM
    item a, transaction b
    WHERE a.item_no (+) = b.tran_no

Posting Permissions

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