Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2004
    Posts
    105

    Unhappy Unanswered: Problems with Joins in Oracle

    Hi everybody i have a question
    i have a programme that so far got its data from a access database.
    Now i need it do work with a oracle database
    in the access version i have lots of Inner Joins and Left Joins
    but they dont work with my oracle database
    is there a way how i can make that work in oracle too?
    like if i have a code in access for example like that :

    Select tableA.ID,tableB.Name, tableC.ID
    From (tableA LEFT JOIN tableC ON tableC.ID =tableA.ID)
    LEFT JOIN tableB ON tableB.ID = tableA.ID
    Where tableB.Name = "Name"

    or

    Select DISTINCT tableA.ID
    From tableA
    Inner JOIN tableB ON tableB.ID = tableA.ID
    Where Not tableA.ID = '1213214'

    how could i make such Joins in oracle?
    Thanks a real lot
    Last edited by justme:-); 02-07-05 at 10:51.

  2. #2
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    Which Oracle version?

  3. #3
    Join Date
    Oct 2004
    Posts
    105
    its a Oracle 8 i database

  4. #4
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    Ansi joins are supported in 9i onwards. So you will need to change your code to use the older Oracle theta style joins. Something like this:

    Code:
    SELECT a.id
         , b.name
         , c.id
    FROM tableA a
       , tableB b
       , tableC c
    WHERE a.id(+) = c.id
    AND b.id(+) = a.id
    AND b.Name = "Name" 
    
    
    SELECT DISTINCT a.id 
    FROM tableA a
       , tableB b
    WHERE a.id = b.id
    AND a.id != '1213214'

  5. #5
    Join Date
    Oct 2004
    Posts
    105
    ah ok looks like the joins are even easier in oracle then in access thanks a real lot!

  6. #6
    Join Date
    Oct 2004
    Posts
    105
    hm i tried the joins but i have a problem there
    i tried to make a left join like that
    Where TableA.ColumnA(+) =TAbleB.ColumnD
    but the output seems to be a inner join
    cause like if i have two tables
    tableA like this columnA
    columnB
    columnC
    and TableB with columnD
    columnE
    now i want to make a left join so that
    the outputtable has columnA, ColumnB, ColumnC, ColumnD and ColumnD
    where the rows shall have data in the columns C and D in which there is a relatet data in the columns of Table a and No data in the columns for C and D where there is no related data in the colums of Table A like
    TableA TableB
    ColumnA ColumnB ColumnC and ColumnD ColumnE
    a1 b1 c1 a1 e1
    a2 b2 c2 a2 e2
    a3 b3 c3
    a4 b4 c4

    the output after the left join i need would be like that then

    a1 b1 c1 e1
    a2 b2 c2 e2
    a3 b3 c3 NULL
    a4 b4 c4 NULL

    what im getting so far is this
    a1 b1 c1 e1
    a2 b2 c2 e2

    somebody now what i do wrong here?
    thanks

  7. #7
    Join Date
    Oct 2004
    Posts
    105
    oh i found out i just have to do it the other way around and make
    tableB.ColumnD (+)= tableA.ColumnA
    i dont really now the logic behind it though

  8. #8
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    Try these:

    Code:
    SELECT a.columna
         , a.columnb
         , a.columnc
         , b.columne
    FROM tableA a
       , tableB b
    WHERE a.columna(+) = b.columnd;
    
    
    COLUMNA    COLUMNB    COLUMNC    COLUMNE
    ---------- ---------- ---------- ----------
    a1         b1         c1         e1
    a2         b2         c2         e2
    
    2 rows selected.
    and...
    Code:
    SELECT a.columna
         , a.columnb
         , a.columnc
         , b.columne
    FROM tableA a
       , tableB b
    WHERE a.columna = b.columnd(+);
    
    
    COLUMNA    COLUMNB    COLUMNC    COLUMNE
    ---------- ---------- ---------- ----------
    a1         b1         c1         e1
    a2         b2         c2         e2
    a3         b3         c3
    a4         b4         c4
    
    4 rows selected.

  9. #9
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    Our posts crossed in the ether. It all depends on which way round the join is completed ie. which table is joined to which. It all depends where you start from. For your problem I would use:
    Code:
    SELECT a.columna
         , a.columnb
         , a.columnc
         , b.columne
    FROM tableA a
       , tableB b
    WHERE b.columnd(+) = a.columna;

  10. #10
    Join Date
    Oct 2004
    Posts
    105
    WHERE b.columnd(+) = a.columna;

    Ah ok so the code abouve would mean that columnb is joined to columna?
    Thanks

  11. #11
    Join Date
    Oct 2004
    Posts
    105
    i already have another question
    in access i used the IIF and changed this code for oracle like that

    SELECT DISTINCT IIF(tableA.Name IS NULL,
    '<returniftrue>', tableA.Name)
    FROM tableA, TableB
    Where TAbleA (+)= tableB
    and TableB.columnA = 'abcde'

    meaning if for abcde there is no entry in tableA.Name then it shall return "<returniftrue>" and if there is a entry then it shall return the name
    it has not been working with the oracle though does oracle not know IIF?

    thanks

  12. #12
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    IIF must be ACCESS-specific.

    In Oracle, you can use NVL :
    Code:
    select NVL(tableA.Name, 'default_value')
    from tableA
    That will return tableA.Name if tableA.Name is not NULL, and "default_value" if tableA.Name is NULL.

    You can also use DECODE if you want different throughput even for non-null values. Have a look at Oracle SQL Reference Manual for more info about SQL Functions available in Oracle.

    Regards,

    RBARAER

  13. #13
    Join Date
    Oct 2004
    Posts
    105
    the nvl works great thanks a real lot

  14. #14
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    TO (gannet)

    HI gannet

    IN YOUR POST I FOUND (Ansi joins are supported in 9i onwards)

    what do you mean by (Ansi joins ) ????
    do u mean (American National standars Institute

    because I work on oracle 9i, maybe this will help me

    do u have any documents about this subject

    thanx in advanec

  15. #15
    Join Date
    Jan 2004
    Posts
    492
    Yes - from 9i onward, you can either use (+) or ANSI outer join notation - your choice!

    http://download-west.oracle.com/docs...3a.htm#2066612
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

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