Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: SQL Server equivalent for the Oracle join query

    Hi,

    Kindly give the SQL Server equivalent for the below Oracle query :

    select *from t1, t2, t3 where
    t1.t1col1 (+) = t2.t2col1 and
    t2.t2col1 (+) = t3.t3col1

    Thanks,
    Sam

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I believe that's a right join

    Code:
        SELECT *
          FROM t1
    RIGHT JOIN t2
    	ON t1.t1col = t2.t2col
    RIGHT JOIN t3
    	ON t2.t2col = t3.t3col
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice formatting style, brett

    i believe it's a left join:
    Code:
    select *
      from t3
    left outer
      join t2
        on t3.t3col1
         = t2.t2col1
    left outer
      join t1
        on t2.t2col1 
         = t1.t1col1
    sam, would you please be good enough to test both versions? and let us know which one worked the way you expected?

    please make sure there are missing rows from all tables so that we can see the effect of the double outerness...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2004
    Posts
    205
    Thank you and I will get back to you.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm pretty sure that's a Right Join...it's been awhile...but...

    Code:
    -- *********************************************************************
    -- *** P R O C E D U R E (Get_OptLongTermDisEl_sp) D E C L A R A T I O N S 
    -- *********************************************************************
      PROCEDURE Get_OptLongTermDisEl_sp   (I_EMPLID          IN  VARCHAR2,
                           		     OptLongTermDisElCur OUT CurRefType) IS
      BEGIN
    
    --* Retrieve Optional Long Term Disability eligible options.
    
       OPEN OptLongTermDisElCur  FOR SELECT  	
    				 A.PLAN_TYPE
    				,A.OPTION_TYPE
    				,A.BENEFIT_PLAN
    				,A.OPTION_CD
    				,B.annual_amt
    				,B.DEDUCT_AMT
    			FROM ENR_PARTIC_OPTN A
    			    ,ENR_PARTIC_COST B
    			WHERE A.EMPLID    = I_EMPLID
    			  AND A.PLAN_TYPE LIKE '3%' 
    			  AND A.EMPLID    = B.EMPLID(+)
    			  AND A.PLAN_TYPE = B.PLAN_TYPE(+)
    			  AND A.OPTION_ID = B.OPTION_ID(+);
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm even more sure that it's a left outer join

    what was that code for? i don't get it, but i do notice that the plus signs are over on the other side, compared to where they are in post #1

    they can't be on opposite sides and still both be right joins, now, can they?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    what was that code for? i don't get it, but i do notice that the plus signs are over on the other side
    It's an example of a LEFT JOIN
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    "Nope"? that's it? just "nope"?

    you're saying they can't both be the same?

    i beg to differ, and i invite you to look at the queries in this thread again

    thanks for the link to that article, though (it did not clear things up at all about the equivalence of left and right joins, now, did it? can't you tell i've been sort of leading you in a particular direction?)

    i looked around that site for a while and did not see a single scrap of original content

    guy sure looks like he's got a nice little income generating site there, though

    hmm, i've been thinking for a long time about putting ads on my own site, it might just be time, seeing how eagerly guys like you might link to it -- whaddya think?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mineselbst
    i looked around that site for a while and did not see a single scrap of original content
    correction: this comment was intended to describe tshcomputing.com -- that oracle-base site is actually full of his content (too bad i don't use oracle, eh)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, speak slowly and use little words, because I'm very confused.

    Are you saying that a Left Join and a Right Join are the same?

    That your query and mine would produce the same results?

    In your case table 1 is the driver, in mine it's table 3.

    I'm confused.

    Damn hangover
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are beginning to see the light, grasshopper

    yes, take a very close look at the queries

    and soon, you will understand why i never write right outer joins

    but you must know why i don't, and why i can get away with it, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [doooh]
    You know, it helps when you actually read these things...You would think I was some blind man or something
    [/doooh]

    BUT! Their syntax IS a right join, and Yes I rarley use a right join, but I gave the customer what they asked for..

    I thought you had some fundamental thing I was missing...

    You was just having fun....

    The point of this story is that you can do it either way...You just have to make sure the tables are in the correct order....

    "I'm gonna need a beer to put these flames out..."
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    You just have to make sure the tables are in the correct order....
    exactly, which is why i always start with the first table mentioned being the one i want all the rows of

    in other words, if i start out from t1 then i know t1 is the one which will return all its rows, and now i attempt to join other rows, maybe i'll find them, maybe not

    this is particularly important when you write
    Code:
      from table1
    left outer
      join table2
        on table1.pk 
         = table2.fk
       and table2.foo = 'bar'
    i'm sure you know what happens if the condition on foo is moved from the ON clause to the WHERE clause

    anyhow, that's why i always write everything as a left outer and never as a right outer

    it's just tons easier to understand
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh, very funny Mr. Kaiser.

    I DO read these things, thank you very much. I just also know when to keep my mouth shut (or fingers still).


    ...well...sometimes, anyway...

    blind man
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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