Return Value OR NULL

[brandonBuster]

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, l.name, s.score
   from scores s
   inner join locations l on l.id = 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, l.name

[r937]

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

SELECT y.yr
     , m.mth
     , data.name
     , data.score
  FROM ( SELECT 2012 AS yr
         UNION ALL SELECT 2013 ) AS yrs
CROSS 
  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           
LEFT OUTER
  JOIN ( SELECT s.month
              , s.year
              , l.name
              , s.score
           FROM scores s
         INNER 
           JOIN locations l 
             ON l.id = 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
ORDER 
    BY y.yr
     , m.mth
     , data.name

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

[brandonBuster]

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.