Left Join With Where Clause

Question

I need to retrieve all default settings from the settings table but also grab the character setting if exists for x character    But this query is only retrieving those settings where character is   1  not the default settings if the user havent setted anyone   SELECT  settings      character settings   value  FROM   settings   LEFT JOIN  character settings   ON  character settings   setting id     settings   id  WHERE  character settings   character id     1      So i should need something like this   array       0    gt  array  somekey    gt   keyname    value    gt   thevalue         1    gt  array  somekey2    gt   keyname2         2    gt  array  somekey3    gt   keyname3       Where key 1 and 2 are the default values when key 0 contains the default value with the character value

User · Answer

When making OUTER JOINs  ANSI-89 or ANSI-92   filtration location matters because criteria specified in the ON clause is applied before the JOIN is made   Criteria against an OUTER JOINed table provided in the WHERE clause is applied after the JOIN is made   This can produce very different result sets   In comparison  it doesn t matter for INNER JOINs if the criteria is provided in the ON or WHERE clauses -- the result will be the same     SELECT  s               cs  value       FROM SETTINGS s LEFT JOIN CHARACTER SETTINGS cs ON cs setting id   s id                                AND cs character id   1

User · Answer

For this problem  as for many others involving non-trivial left joins such as left-joining on inner-joined tables  I find it convenient and somewhat more readable to split the query with a with clause  In your example   with settings for char as     select setting id  value from character settings where character id   1   select   settings      settings for char value from   settings   left join settings for char on settings for char setting id   settings id

User · Answer

You might find it easier to understand by using a simple subquery  SELECT  settings           SELECT  value  FROM  character settings      WHERE  character settings   setting id     settings   id        AND  character settings   character id     1   AS cv value FROM  settings    The subquery is allowed to return null  so you don t have to worry about JOIN WHERE in the main query   Sometimes  this works faster in MySQL  but compare it against the LEFT JOIN form to see what works best for you   SELECT s    c value FROM settings s LEFT JOIN character settings c ON c setting id   s id AND c character id    1

User · Answer

The where clause is filtering away rows where the left join doesn t succeed   Move it to the join   SELECT   settings      character settings   value  FROM     settings  LEFT JOIN          character settings   ON      character settings   setting id     settings   id          AND  character settings   character id     1

User · Answer

The result is correct based on the SQL statement  Left join returns all values from the right table  and only matching values from the left table   ID and NAME columns are from the right side table  so are returned   Score is from the left table  and 30 is returned  as this value relates to Name  Flow   The other Names are NULL as they do not relate to Name  Flow    The below would return the result you were expecting       SELECT  a    b Score FROM     Table1 a     LEFT JOIN  Table2 b        ON a ID   b T1 ID  WHERE 1 1 AND a Name    Flow    The SQL applies a filter on the right hand table

User · Answer

If I understand your question correctly you want records from the settings database if they don t have a join accross to the character settings table or if that joined record has character id   1   You should therefore do  SELECT  settings      character settings   value  FROM   settings   LEFT OUTER JOIN  character settings   ON  character settings   setting id     settings   id  WHERE  character settings   character id     1  OR  character settings  character id is NULL

[sql] Left Join With Where Clause

Examples related to sql

Examples related to left-join

Examples related to where-clause