sqlite self join with where clause

Question

I have a table that consists of names, points, and years. I need a command to return all the names for a specific year even if the name isn't included in that year. Example

Name        Points    Year
-------    ------- 
tom           8       2011
jim           45      2011
jerry         25      2011
zack         124      2011
jeff          45      2011
tom           62      2012
jim          214      2012
jerry         13      2012
zack          32      2012
arnold         4      2012




Name        Points    Year
-------    ------- 
tom           8       2011
jim           45      2011
jerry         25      2011
zack         124      2011
jeff          45      2011
arnold       NULL     NULL

I figured this would be easy but I am struggling to make it work.

Answer 1

From your explanation, I'm thinking you need could use something like this:

SELECT DISTINCT
    N.`Name`,
    D.`Points`,
    Y.`Year`
FROM
    `MyData` Y
LEFT JOIN (SELECT DISTINCT `Name` FROM `MyData`) N ON 1=1
LEFT JOIN `MyData` D 
    ON  D.`Year` = Y.`Year` 
    AND D.`Name` = N.`Name`
ORDER BY 
    Y.`Year`

While It's not pretty, It does seem to work as intended: