S D
sqlserver667 at yahoo.com
Fri Apr 2 00:10:33 CST 2004
Hi Francisco, you are correct. I have never used them. I never took time to figure it out. I always wondered if there was a diff. I'll take you're advise and start hacking the statistics. SQL Server is just a (very) small portion of my working field. Most of it is MS-Access...and there is no query-plan thingy in Access :-( Sander Francisco H Tapia <my.lists at verizon.net> wrote: I personnally stay away from where joins. I've seen them all to often yield different results.. perhaps the developer didn't know how to use them, but I've always stuck to INNER/LEFT/RIGHT Joins... and specifically because you will tend to yield a better query plan. Have you run the statistics to see how many table scans you get from joining in the where clause? S D said the following on 4/1/2004 4:37 AM: >Hi paul, > >nice to see you here also. > >Personally I do NOT use the INNER JOIN stuff. I just use plain SQL >SELECT a.x, b.y >FROM table1 a, table2 b >WHERE a.a = b.a >AND .... >That would result in: >UPDATE tblPersonnelJobCategories > SET tblPersonnelJobCategories.Selected = True > WHERE tblPersonnelJobCategories.UserID= @UserId > AND tblPersonnelJobCategories.CategoryID = tblPersonnelCategoriesSelected.CategoryID > AND tblPersonnelJobCategories.PayrollNo = tblPersonnelCategoriesSelected.PayrollNo > > >A tip for you're query: does it run when you hardcode the UserID? > >Did you try to run: >SELECT tblPersonnelJobCategories.UserID, > tblPersonnelJobCategories.Selected > FROM tblPersonnelJobCategories, > tblPersonnelCategoriesSelected > WHERE tblPersonnelJobCategories.UserID= @UserId > AND tblPersonnelJobCategories.CategoryID = tblPersonnelCategoriesSelected.CategoryID > AND tblPersonnelJobCategories.PayrollNo = tblPersonnelCategoriesSelected.PayrollNo > >Success. > >Sander > > >paul.hartland at fsmail.net wrote: >To all, > >I have a Visual Basic 6 front-end together with a SQL Server 7.0 back-end, and have tried writing a stored procedure to do the following: > >I pass a UserID into the SP, then I want the SP to UPDATE tblPersonnelJobCategories to TRUE where tblPersonnelJobCategories.UserID is equal to the UserID that I passed to the SP but I only want the UPDATE to take place where the PayrollNo & CategoryID in tblPersonnelJobCategories matches the PayrollNo & CategoryID in tblPersonnelCategoriesSelected > >I tried using the follwing SQL statement in my SP but it doesn’t like the Inner Join: > >UPDATE tblPersonnelJobCategories INNER JOIN tblPersonnelCategoriesSelected ON (tblPersonnelJobCategories.CategoryID = tblPersonnelCategoriesSelected.CategoryID) AND (tblPersonnelJobCategories.PayrollNo = tblPersonnelCategoriesSelected.PayrollNo) SET tblPersonnelJobCategories.Selected = True WHERE tblPersonnelJobCategories.UserID= @UserId > >Any help would be greatly appreciated, as I’m fairly new to SP’s and thought an INNER JOIN would be ok. > > -- -Francisco _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com Do you Yahoo!? Yahoo! Mail - More reliable, more storage, less spam