SQL Server Performance

Converting non ANSI *= to Left Join

Discussion in 'SQL Server 2008 General Developer Questions' started by lrac, Apr 28, 2009.

  1. lrac New Member

    Hi thereI have been handed this problem once our web server was upgraded to MS SQL 2008. Could someone help me change this to the proper LEFT OUTER JOIN?My SQL knowledge is not advanced enough to change this unfortunately.The stored procedure currently looks like this, (and at the moment I can't even get it to execute)Begin of pasted code...GO/****** Object: StoredProcedure [dbo].[ospw_cases] Script Date: 04/28/2009 08:54:31 ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGO--ALTER ProcALTER PROCEDURE [dbo].[ospw_cases](--Input Parameters@clientNovarchar(10))ASSELECT M.client_no, M.matter_no, M.matter_description as description,W.work_description, F.fee_earner_description as solicitor, CAST(COALESCE(COUNT(P.matter_no),0) AS varchar(4))+' New' as Messages,E.fee_email_address as emailFROM matters AS M, fecodes as F, worktype as W, pubmessage as P, feemail as EWHERE m.work_type = W.work_idAND M.fee_earner = F.fee_earner_codeAND F.fee_earner_code *= E.fee_earner_code AND M.client_no *= P.client_no AND M.matter_no *= P.matter_no AND M.client_no = @clientNoAND M.Web_enabled = 1AND P.msg_status <> 'SENT'left join Feemail on F.fee_earner_code = E.Fee_earner_codeGROUP BY M.client_no, M.matter_no, M.matter_description, W.work_description, F.fee_earner_description, E.fee_email_address If @@ERROR <> 0BeginReturn 1EndElseBeginReturn 0EndEnd of Pasted Code.Any help here would be greatly appreciated.ThanksPS Sorry for bad formatting but I cant seem to get it to show new lines.. :(
  2. dhamu294 New Member

    hi where ever you get the * add a left outer join in the from list and have the same condition in after on statement. get back in case of any doubts. i am not able to see the query clearly but you can do this.
  3. lrac New Member

    hi, many thanks for replying so promptly. Maybe if I can put it like this you will be able to see more clearly
    Unfortunately my SQL knowledge stops after select * from ......
    If you could be so kind as to reformat this into sql2008 code it would be greatly appreciated.
    SELECT M.client_no,
    M.matter_no,
    M.matter_description as description,
    W.work_description,

    F.fee_earner_description as solicitor,
    CAST(COALESCE(COUNT(P.matter_no),0) AS varchar(4))+' New' as Messages,
    E.fee_email_address as email
    FROM matters AS M, fecodes as F, worktype as W, pubmessage as P, feemail as E

    WHERE m.work_type = W.work_id
    AND M.fee_earner = F.fee_earner_code
    AND F.fee_earner_code *= E.fee_earner_code
    AND M.client_no *= P.client_no
    AND M.matter_no *= P.matter_no
    AND M.client_no = @clientNo
    AND M.Web_enabled = 1
    AND P.msg_status <> 'SENT'
    I have a problem putting this complicated code above into the format that u suggested :S
    I appreciate that this is free help - and I really do appreciate any time on this to help me.
    Thank you again
  4. Adriaan New Member

    Does your knowledge stretch to JOIN syntax?
    The *= operators and fields must be moved to the ON clause of a LEFT JOIN.
    The = operators and fields that connect tables must be moved to the ON clause of an INNER JOIN.
    Finally, the filter criteria stay in the WHERE clause
    FROM matters AS M
    INNER JOIN worktype AS Q ON M.work_type = W.work_id
    LEFT JOIN fecodes AS F ON M.fee_earner = F.fee_earner_code
    etc.
  5. lrac New Member

    Once again, many thanks for prompt replies.
    At the risk of appearing really stupid..... I have to ask another question before I can even try out this suggestion as mentioned above.
    @Adriaan, no, my knowledge does not stretch to cover JOIN syntax (as is probably pretty clear to all by now :) )
    http://img228.imageshack.us/my.php?image=sqlcodingproblems1.jpg
    Could I ask you to advise me further on this issue?
    Thank you
  6. Adriaan New Member

    If you do not understand the re-write that I did for a couple of the joins, then you should start learning SQL soon. It's not that difficult, you know.[;)]
  7. lrac New Member

    <p>[quote user="Adriaan"]</p><p>If you do not understand the re-write that I did for a couple of the joins, then you should start learning SQL soon. It's not that difficult, you know.<img src="http://sql-server-performance.com/Community/emoticons/emotion-5.gif" mce_src="http://sql-server-performance.com/Community/emoticons/emotion-5.gif" alt="Wink"></p>[/quote]&nbsp;<p>Yes, /bow ... </p><p>&nbsp;</p><p>Thank you for the time and trouble all have put in this - I have finally got it working :) <br></p><p>&nbsp;</p><p>(but i still need to learn more SQL)&nbsp; ;)<br></p><p>&nbsp;</p><p>Thanks again</p><p>A grateful system admin. . <br></p>

Share This Page