I am a big fan of the subquery join when you have complex queries. This allows you to reference the subquery's aliases by name instead of by aggregated expression, which simplifies SQL legibility. It is the basis for the idea of a view.
On 3/28/07, Jim Wright <[EMAIL PROTECTED]> wrote: > > Douglas Fentiman wrote: > >> Jim Wright wrote: > >> > >> Move the user_id condition up into the join.... > >> > > > > So obvious now that I see it. I'm not very experienced with SQL and was > being a little too rigid in interpreting the rules... MySQL_5 doc's state: > > > > "You should generally not have any conditions in the ON part that are > used to restrict which rows you want in the result set, but rather specify > these conditions in the WHERE clause. There are exceptions to this rule." > > > > I should have payed more attention to the last sentence... > > > > Thanks Jim! > > > Hmmm...I've never had a problem using a join predicate like that, and in > fact SQL Server Books Online has an example in their description of > outer joins...perhaps they are not as optimized in MySQL...another way > of doing it would be to join on a subquery... > > SELECT > la.Language_name > , la.Language_id > , ul.user_id > , ul.LanguageSpeak > , ul.LanguageWrite > , ul.LanguageRead > FROM > Languages la LEFT JOIN (SELECT * FROM user_languages WHERE User_Id = 1) > ul ON la.Language_id = > ul.Language_id > > I'm not sure that gains you anything, though. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2803 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
