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

    Unhappy Unanswered: problems with a sql - query (left joins)

    Hi all i have a problem i have this sql code but it does not work

    Select DISTINCT Table1.A, Table2.B, Table3.C
    From Table1, Table2, Table3
    Where Table1.B (+)= Table3.B
    And Table1.A (+)= Table2.A

    the sql code was first for a access database like that

    Select DISTINCT Table1.A, Table2.B, Table3.C
    From ( Table1 LEFT JOIN Table3
    ON Table3.B = Table1.B)
    LEFT Table2
    ON Table2.A = And Table1.A

    how can i put that code in to a code for oracle to get me the same result
    thanks a real lot
    Last edited by justme:-); 02-10-05 at 07:20.

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    "spl" ? Is that a new language ???

    I think this should do what you did in Access (you just forgot 'JOIN' in you second LEFT JOIN) :
    Code:
    Select DISTINCT Table1.A, Table2.B, Table3.C
    From (Table1 LEFT JOIN Table3
    ON Table3.B = Table1.B) 
    LEFT JOIN Table2
    ON Table2.A = And Table1.A;
    BTW, Oracle recommends ANSI syntax (LEFT JOIN) in comparison with the old Oracle-specific syntax (+). From the Oracle SQL Reference (9i) :
    Code:
    Outer Joins
    An outer join extends the result of a simple join. 
    An outer join returns all rows that satisfy the join condition 
    and also returns some or all of those rows from one table 
    for which no rows from the other satisfy the join condition.
    
    - To write a query that performs an outer join of tables A and B 
    and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN 
    syntax in the FROM clause, or apply the outer join operator (+) to all 
    columns of B in the join condition in the WHERE clause. For all rows in A 
    that have no matching rows in B, Oracle returns null for any select list
    expressions containing columns of B.
    
    - To write a query that performs an outer join of tables A and B and returns
    all rows from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in 
    the FROM clause, or apply the outer join operator (+) to all columns of A 
    in the join condition in the WHERE clause. For all rows in B that have no 
    matching rows inA, Oracle returns null for any select list expressions 
    containing columns of A.
    
    - To write a query that performs an outer join and returns all rows from A 
    and B, extended with nulls if they do not satisfy the join condition (a full 
    outer join), use the FULL [OUTER] JOIN syntax in the FROM clause.
    
    Oracle Corporation recommends that you use the FROM clause OUTER JOIN 
    syntax rather than the Oracle join operator.
    You can read more here.

    HTH & Regards

    RBARAER

  3. #3
    Join Date
    Oct 2004
    Posts
    105
    sorry for my bad spelling

    ah i forgot to say i have a oracle 8 version it uses (+)= for the left joins
    also i have difficulties with the order of the joins like

    if i have a Left join in access like that

    TableA LEFT JOIN TableB ON TableB.c = TableA.c
    meaning it takes All data from TableA and ads only the data from TableB who has corresponding data in Table A

    is this in Oracle then

    from TableA, TableB
    Where TableA.c (+)= TableB.c

    or the other way around?

    from TableA, TableB
    Where TableB.c (+)= TableA.c
    Last edited by justme:-); 02-10-05 at 07:45.

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Sorry for that, I thought you were working with 9i.

    As said in the excerpt from the doc I showed you,

    "from TableA LEFT JOIN TableB ON TableB.c = TableA.c"

    is equivalent to

    "from TableA, TableB
    Where TableB.c (+)= TableA.c"

    So indeed, I think that your (+) are not placed well in your first query.

    Regards,

    RBARAER
    Last edited by RBARAER; 02-10-05 at 08:12.

  5. #5
    Join Date
    Oct 2004
    Posts
    105
    ah ok just wanted to make sure i tried but got mixed up somewhere thanks a lot

Posting Permissions

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