Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Nov 2007
    Posts
    3

    Question Unanswered: Please Help! Doing a join in MS SQL Server 2005

    Hi all,
    I have a query that someone sent me in which I need to do an outer join on two columns. I am used to using Oracle but I need to do this in MS SQL Server 2005. The query is:

    Code:
    select i.is_issue_no as NUMBER, i.IS_STATUS as STATUS, i.is_received_datetime as DATE_OPENED, i.IS_RESOLVED_DATETIME as DATE_CLOSED, c.cm_full_name as NAME, r.ct_data as MANAGER, co.co_company_name COMPANY, i.is_summary as NOTES, it.ISTY_ISSUE_TYPE_NAME as TYPE, ic.ICG_CATEGORY_NAME as CATEGORY
    from customers c, cdtbl_customers_reporting_mgr r, issues i, ISSUE_CATEGORIES ic, ISSUE_TYPES it, companies co
    where i.IS_CATEGORY=ic.ICG_CATEGORY_ID and i.IS_ISSUE_TYPE_ID=it.ISTY_ISSUE_TYPE_ID and c.CM_COMPANY_ID = co.CO_COMPANY_ID and ((c.reporting_mgr=r.ct_code and i.is_customer_id=c.cm_customer_id)
    and (i.is_received_datetime > '20081000000000' and i.is_received_datetime < '20081100000000'))
    order by i.is_issue_no desc
    I need to do an outer join on i.IS_CATEGORY=ic.ICG_CATEGORY_ID. Normally (in Oracle) I would throw a (+) at the end of this and that would be the outer join. How do I do this in SQL Server?

    I am really pressed to get this resolved so any help would EXTREMELY appreciated.

    Russ

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    The syntax is LEFT OUTER JOIN.

    You can read about it in Books Online in the transact sql reference under SELECT.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Really now.

    Rewrite your code using JOIN syntax and it is simpler, and clearer:
    Code:
    select	i.is_issue_no as NUMBER,
    	i.IS_STATUS as STATUS,
    	i.is_received_datetime as DATE_OPENED,
    	i.IS_RESOLVED_DATETIME as DATE_CLOSED,
    	c.cm_full_name as NAME,
    	r.ct_data as MANAGER,
    	co.co_company_name COMPANY,
    	i.is_summary as NOTES,
    	it.ISTY_ISSUE_TYPE_NAME as TYPE,
    	ic.ICG_CATEGORY_NAME as CATEGORY
    from	customers c,
    	inner join cdtbl_customers_reporting_mgr r on c.reporting_mgr=r.ct_code
    	inner join issues i on i.is_customer_id=c.cm_customer_id
    	left outer join ISSUE_CATEGORIES ic on i.IS_CATEGORY=ic.ICG_CATEGORY_ID
    	inner join ISSUE_TYPES it on i.IS_ISSUE_TYPE_ID=it.ISTY_ISSUE_TYPE_ID
    	inner join companies co on c.CM_COMPANY_ID = co.CO_COMPANY_ID
    where	i.is_received_datetime > '20081000000000'
    	and i.is_received_datetime < '20081100000000'
    order by i.is_issue_no desc
    Also, get rid of all those silly single-letter aliases if you want your code to be more readable.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Nov 2007
    Posts
    3
    Awesome!!! thank you very much!!! Worked like a charm.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT i.is_issue_no           AS NUMBER
         , i.IS_STATUS             AS STATUS
         , i.is_received_datetime  AS DATE_OPENED
         , i.IS_RESOLVED_DATETIME  AS DATE_CLOSED
         , c.cm_full_name          AS NAME
         , r.ct_data               AS MANAGER
         , co.co_company_name      AS COMPANY
         , i.is_summary            AS NOTES
         , it.ISTY_ISSUE_TYPE_NAME AS TYPE
         , ic.ICG_CATEGORY_NAME    AS CATEGORY
      FROM issues        AS i
    INNER
      JOIN ISSUE_TYPES   AS it 
        ON it.ISTY_ISSUE_TYPE_ID = i.IS_ISSUE_TYPE_ID
    INNER
      JOIN customers     AS c
        ON c.cm_customer_id = i.is_customer_id 
    INNER
      JOIN companies     AS co 
        ON co.CO_COMPANY_ID = c.CM_COMPANY_ID
    INNER
      JOIN cdtbl_customers_reporting_mgr AS r 
        ON r.ct_code = c.reporting_mgr
    LEFT OUTER 
      JOIN ISSUE_CATEGORIES  AS ic 
        ON ic.ICG_CATEGORY_ID = i.IS_CATEGORY
     WHERE i.is_received_datetime > '20081000000000'
       AND i.is_received_datetime < '20081100000000'
    ORDER 
        BY i.is_issue_no DESC
    thanks for using 1-letter table aliases, it makes the query a lot easier to understand
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Damn you, Rudy!

    ...or should I just say "DY,R!"
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    More like.....

    UPDATE EXISTENCE.UNIVERSE.HUMAN SET STATUS = "DAMNED"
    WHERE EXISTENCE.UNIVERSE.HUMAN.NAME = "RUDY"
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Thrasymachus
    STATUS = "DAMNED"
    Error BigOmega24937: column "DAMNED" not found
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Thrasymachus
    More like.....

    UPDATE EXISTENCE.UNIVERSE.HUMAN SET STATUS = "DAMNED"
    WHERE EXISTENCE.UNIVERSE.HUMAN.NAME = "RUDY"
    (1437891 row(s) affected)

    Oh crap.....
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    psuedo code and i think you can set your options about qoutes.

    and there can only be one rudy.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Thrasymachus
    and there can only be one rudy.
    don't i wish

    there's the movie about the guy who played football at notre dame, there's the guy himself, there's the former mayor of new york, then the sunglasses, and then, if i'm lucky, there's me

    i'm not the only rudy, but i am on page 1 of the search results

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

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I've had a superhero and an animated shark named after me, and Monty Python once did an entire skit about me.
    Not too shabby.
    If it's not practically useful, then it's practically useless.

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

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...oh, and a movie about my supernatural powers.
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    pffffft. I am a character in one of the great works of western civilization and a drinking buddy of Socrates.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry, but I still imagine Thrasymachus as a bipedal reptile from the Cretaceous period.
    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
  •