Return/output Non-Existing Row

[yii_king (Micho23)]

I have two tables:

tbl_car

car_id (primary key)

car_model

max_speed

tbl_car_measure

car_model

date

speed_hour1

speed_hour2

…

speed_hour24

I am retrieving all car measurements and get a result set that currently look like this:

car_model, date, speed_hour1, speed_hour2

BMW 330, 2013-06-20, 100, 102

BMW 330, 2013-06-21, 101, 100

BMW 335, 2013-06-20, 100, 103

When there are no car measurements for BMW335 on 2013-06-21 (no row in the db), I’d still like to have an empty row returned so that the result would like this:

car_model, date, speed_hour1, speed_hour2

BMW 330, 2013-06-20, 100, 102

BMW 330, 2013-06-21, 101, 100

BMW 335, 2013-06-20, 100, 103

BMW 335, 2013-06-21, NULL, NULL

How I can get this result back from my MSSQL 2008 database? Or should I be looking at doing this at PHP/Yii level?

[yii_king (Micho23)]

The current query look like this:

SELECT car_model, date, speed_hour1, speed_hour2 FROM tbl_car as c, tbl_car_measure as cm

WHERE (cm.date >= ‘2013-06-20’ AND cm.date <= ‘2013-06-22’) AND c.car_model = cm.car_model