Return Value OR NULL

I have a query that returns a score for each month per company location. The months are specified per a given time span. The trouble is, not every company location will have data for every month within the time span.

Is there a way to modify the below query (perhaps using a correlated sub query) to return the score per location/month, or NULL if the location has no recorded monthly value?

select s.month, s.year,, s.score
   from scores s
   inner join locations l on = s.location_id and l.company_id = s.company_id
   where s.company_id = 22
   and s.year*100+m between 201203 and 201207
   group by s.location_id, s.month, s.year
   order by s.year, s.month,

you’ll need to have a month table as the left table in a LEFT OUTER JOIN

it’s a little trickier to make this a general purpose solution because you’ve separated the year and month into two different columns

so, we’ll use a generated year table along with a generated month table

     , m.mth
     , data.score
  FROM ( SELECT 2012 AS yr
         UNION ALL SELECT 2013 ) AS yrs
  JOIN ( SELECT 1 AS mth
         UNION ALL SELECT 2         
         UNION ALL SELECT 3         
         UNION ALL SELECT 4         
         UNION ALL SELECT 5         
         UNION ALL SELECT 6         
         UNION ALL SELECT 7         
         UNION ALL SELECT 8         
         UNION ALL SELECT 9         
         UNION ALL SELECT 10         
         UNION ALL SELECT 11         
         UNION ALL SELECT 12 ) AS mths           
  JOIN ( SELECT s.month
              , s.year
              , s.score
           FROM scores s
           JOIN locations l 
             ON = s.location_id 
            AND l.company_id = s.company_id
          WHERE s.company_id = 22 ) AS data
    ON data.year = yrs.yr
   AND data.m = mths.mth
 WHERE yrs.yr*100+mths.mth BETWEEN 201203 AND 201207
    BY y.yr
     , m.mth

note i removed your GROUP BY clause because you don’t seem to be aggregating anything

Some good stuff here. Thank you always for your help, Rudy. Sorry about the Group By. The sql I posted was a stripped down/simplified version of the production query. Scores are actually a compilation of several columns, which is why the Group By was originally included.