XML FeedsI'm almost embarrassed to publish this but I have operated for years under a false assumption that I only recently discovered to be untrue. Or, to perhaps state it better, to be unnecessary. When using an outer join, I have always tended to put the ON operators in the order of the join itself. So, for example, if my first table in a left join was table1, then my ON operators would always be table1.field1 = table2.field1. A more clear example is below.
- SELECT a.[field1], a.[field2]
- FROM table1 a LEFT JOIN table2 b ON a.[field3] = b.[field3] AND a.[field1] = b.[field1]
- WHERE b.[field1] IS NULL
However, that is syntactically the same as the following (note where the a.[fieldname] and b.[fieldname] now switch around).
- SELECT a.[field1], a.[field2]
- FROM table1 a LEFT JOIN table2 b ON b.[field3] = a.[field3] AND a.[field1] = b.[field1]
- WHERE b.[field1] IS NULL
Both of the code samples above do the same thing. The only issue when using an outer join is the order in which the tables are presented, not the operators in the ON part of the join.
However, that being said, I will still write my code in that fashion as I think it is easier to read and maintain. If nothing else, it is adherence to a standard. So, even if that doesn't match your own standard, it is important to establish a standard and maintain it which will make it easier for future maintenance.
I thing you didnt alias table2 as b. 
You have 3 errors there:
Thanks for the corrections. I've updated so that they're now fixed. 
perhaps you should have said "convention" instead of "standard" because "standard" might cause confusion with regards to the SQL standard
Actually, I have the exact opposite opinion. I prefer referring to the other table first.
I'm using yet another convention; formatting the join so that it really looks like an operator applied to two arguments:
I always list first table first and then the second table, so it would be
gr8 stuff and thank u for sharing ur knowledge and define the join Sql statements.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.