MYSQL Left Join

ndorfnz

Trying to modify this to always return 3 months regardless if something exists in the table or not.

My feable attempt was to create a table with September, October and November in a months table with column name monthsN. Then incorporate a left join...failing miserably.

The SQL is below...

Appreciate any assistance or guidance!!

select sum(distance) as miles, username, monthname(rundate) from log3 
[COLOR="Red"]left join months on monthsN = monthname(rundate)[/COLOR]
where rundate between '2008-09-01' and '2008-11-30' and username = 'joe' group by monthname(rundate), username order by rundate

Lars_Berg

select sum(distance) as miles, 
          username, 
          monthname(rundate) 
  from log3 
  left join months 
     on monthsN = monthname(rundate)
   and rundate between '2008-09-01' and '2008-11-30' 
   and username = 'joe' 
group by monthname(rundate), username 
 order by rundate

ndorfnz

Thanks for the reply.

A couple things:
1) Did not restrict to 'joe', returned everyone
2) Did not restrict to the date range, returned all months

I'll hack with what you gave me and see what I can do...

Thanks again.

NogDog

We may need a bit more info, such as the structures of the tables involved. My guess is that the problem is the left join, which will return all rows in log3 regardless of whatever where conditions apply to the months table. Perhaps you just need an inner join, instead?