Douglas Fentiman wrote:
> Left Join Problems
>
> I have two tables. One contains languages and the other contains userâs
> language skills. I need a query that will return all languages and the
> language skills for specified user. Language skills for other languages not
> associated to the user are null.
>
> The query below returns all languages not used in the user_languages table
> and the language and language skills associated to specified user_id. Problem
> is that the result does not include the languages associated with other
> user_ids in the user_languages table.
>
> [using MySQL 5]
>
> SELECT
> la.Language_name
> , la.Language_id
> , ul.user_id
> , ul.LanguageSpeak
> , ul.LanguageWrite
> , ul.LanguageRead
> FROM
> Languages la LEFT JOIN user_languages ul ON la.Language_id =
> ul.Language_id
> WHERE
> ((ul.User_id is null) OR (ul.User_id = 1));
>
Move the user_id condition up into the join....
SELECT
la.Language_name
, la.Language_id
, ul.user_id
, ul.LanguageSpeak
, ul.LanguageWrite
, ul.LanguageRead
FROM
Languages la LEFT JOIN user_languages ul ON la.Language_id =
ul.Language_id AND ul.User_id = 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 &
Flex 2
Free Trial
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2800
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
