Hi there I 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 OFF GO SET QUOTED_IDENTIFIER OFF GO --ALTER Proc ALTER PROCEDURE [dbo].[ospw_cases] ( --Input Parameters @clientNo varchar(10) ) AS 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' left join Feemail on F.fee_earner_code = E.Fee_earner_code GROUP BY M.client_no, M.matter_no, M.matter_description, W.work_description, F.fee_earner_description, E.fee_email_address If @@ERROR <> 0 Begin Return 1 End Else Begin Return 0 End End of Pasted Code. Any help here would be greatly appreciated. Thanks PS Sorry for bad formatting but I cant seem to get it to show new lines.. :(

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.

 

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

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.

 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

 

 

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.

Adriaan:

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.

 

Yes, /bow ...

 

Thank you for the time and trouble all have put in this - I have finally got it working :)

 

(but i still need to learn more SQL)  ;)

 

Thanks again

A grateful system admin. .