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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create Web Applications With ColdFusion MX7 & Flex 2.
Build powerful, scalable RIAs. Free Trial
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2802
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
