Results 1 to 4 of 4

Thread: JOIN queries ?

  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: JOIN queries ?

    Hi,

    Can you give the equivalent query for the following Oracle query :

    SELECT col2, col3 FROM submission,
    lkup so
    WHERE so.txt (+) = TRIM(col2)
    AND so.pid (+) = 'SO'
    AND so.cde (+) = 'RW'

    In the above query a constant value is used in JOIN, so I am not aware of how to use the JOIN clause for the above constant value ( 'SO' , 'RW' )

    Upto my knowledge about JOIN query equivalents :

    Oracle query :-

    select *from test1 a, test2 b where a.sno(+) = b.col

    Equivalent SQL Server query :-

    select *from test1 a JOIN test2 b on a.sno = b.col

    Hope the above is correct. Here my doubt is, if in the above query, b.col is replaced by a constant value say 'abcd' , then what will be the table name used in right of the JOIN clause. Or any other equivalent for this is available ?

    Eg :-

    Oracle query :-

    select *from test1 a, test2 b where a.sno(+) = 'abcd'


    Thanks,
    Sam

  2. #2
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hi Sam,

    the syntax used by Oracle with "(+)" in the WHERE-clause is equivalent to an OUTER JOIN in SQL Server and DB2.

    The example you mentioned should be a LEFT OUTER JOIN, but I'm always a bit confused when using OUTER JOINS in Oracle, so I may be wrong :-)

    You can lookup Oracle syntax at http://tahiti.oracle.com
    Carsten

  3. #3
    Join Date
    Mar 2004
    Posts
    205
    Hi, OK, it is RIGHT JOIN, but my requirement is different, Please go through my posting once again. Thanks for your information.

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    SELECT col2, col3 FROM submission,
    lkup so
    WHERE so.txt (+) = TRIM(col2)
    AND so.pid (+) = 'SO'
    AND so.cde (+) = 'RW'


    SELECT col2, col3 FROM submission
    Right Outer Join lkup so
    On so.txt = LTRIM(RTRIM(col2)) AND so.pid = 'SO' AND so.cde = 'RW'

Posting Permissions

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