CODEWITHSUNDEEP
sqlsql-server
jliv902
jliv902

Reputation: 1658

Missing a single day

My database has two tables, a car table and a wheel table.
I'm trying to find the number of wheels that meet a certain condition over a range of days, but some days are not included in the output.

Here is the query:

USE CarDB
SELECT MONTH(c.DateTime1) 'Month',
       DAY(c.DateTime1) 'Day',
       COUNT(w.ID) 'Wheels'
  FROM tblCar c
 INNER JOIN tblWheel w
    ON c.ID = w.CarID
 WHERE c.DateTime1 BETWEEN '05/01/2013' AND '06/04/2013'
   AND w.Measurement < 18
 GROUP BY MONTH(c.DateTime1), DAY(c.DateTime1)
 ORDER BY [ Month ], [ Day ]
GO

The output results seem to be correct, but days with 0 wheels do not show up. For example:

Sample Current Output:

Month Day Wheels
2     1   7
2     2   4
2     3   2 -- 2/4 is missing
2     5   9

Sample Desired Ouput:

Month Day Wheels
2     1   7
2     2   4
2     3   2
2     4   0
2     5   9

I also tried a left join but it didn't seem to work.

Upvotes: 0

Views: 49

Answers (3)

FrankPl
FrankPl

Reputation: 13315

You probably want to use a LEFT OUTER JOIN:

USE CarDB

SELECT MONTH (c.DateTime1) 'Month', DAY (c.DateTime1) 'Day', COUNT (w.ID) 'Wheels'
FROM tblCar c LEFT OUTER JOIN tblWheel w ON c.ID = w.CarID
WHERE c.DateTime1 BETWEEN '05/01/2013' AND '06/04/2013'
  AND (w.Measurement IS NULL OR w.Measurement < 18)
GROUP BY MONTH (c.DateTime1), DAY (c.DateTime1)
ORDER BY [Month], [Day]
GO

Aand then, you need to adapt the WHERE condition, as you want to keep the rows with w.Measurement being NULL due to the OUTER join.

Upvotes: 1

Mike Deluca
Mike Deluca

Reputation: 1210

Remove the join and change your select to this:

SELECT MONTH (c.DateTime1) 'Month', DAY (c.DateTime1) 'Day', isnull(select top 1 (select COUNT from tblWheel where id = tblCar.ID and Measurement < 18), 0)  'Wheels'

Upvotes: 0

gvee
gvee

Reputation: 17171

You were on the right track with a LEFT JOIN

Try run your query with this kind of outer join but remove your WHERE clause. Notice anything?

What's happening is that the join is applied and then the where clause removes the values that don't match the criteria. All this happens before the group by, meaning the cars are excluded.

Here's one method for you:

SELECT Year(cars.datetime1) As the_year
     , Month(cars.datetime1) As the_month
     , Day(cars.datetime1) As the_day
     , Count(wheels.id) As wheels
FROM   (
        SELECT id
             , datetime1
        FROM   tblcar
        WHERE  datetime1 BETWEEN '2013-01-05' AND '2013-04-06'
       ) As cars
 LEFT
  JOIN tblwheels As wheels
    ON wheels.carid = cars.id

What's different this time round is that we're limiting the results of the car table before we join to the wheels table.

Upvotes: 1

Related Questions