SQL Select Statement: How to get all values from Table 'M' and any correlating values from Table 'T' or 0 if nothing.

Question

I am trying to create a query within a commercial ERP system that uses Access as its database, E2 Shop System. So it just runs the query through Access.

I want the query to tell me "how much time was logged on every machine on a prompted date, even if it is 0". I have figured out how to get it to output how much time is on a machine, but only for a machine that has time on it.

Here is the simplified example:

Table: M(machine)

Name  |   ID
------------
Saw   |   1
Drill |   2
Weld  |   3
Lathe |   4
Paint |   5

Table: T(time)

Hours   |  Date  |  Name
-------------------------
8       | 10-25  |  Weld
6       | 10-25  |  Saw
10      | 10-25  |  Lathe
2       | 10-01  |  Drill

The output I want from the query when prompted for Date 10-25 is to get:

Saw   |  6
Drill |  0
Weld  |  8
Lathe |  10
Paint |  0

All I can figure out is this, and it limits the output to only machines that have time against them

SELECT M.name, T.time
FROM M, T
WHERE T.Date = [ENTER DATE POSTED (MM/DD/YY):]))
ORDER BY M.ID ;

I'am thinking that CASE could help, but I can't get it to work.

Answer 1

Use a left join:

SELECT M.Name, COALESCE(T.time, 0)
FROM M
LEFT JOIN T
ON M.Name = T.Name AND T.Date = ...
ORDER BY M.Id

I don't think Access has the COALESCE though... you may be able to use Nz instead.

Related:

• coalesce alternative in Access SQL

Answer 2

The WHERE statement excludes records where the date is null, which is the case for the time table, so you need to add Or Is Null. This can get more complicated, depending on the records you want returned, you may need a sub-query.

SELECT M.Name, Nz([Hours],0) AS Hrs
FROM Machine AS M 
LEFT JOIN [Time] AS T 
ON M.Name = T.Name
WHERE T.Date=#10/25/2010# Or T.Date Is Null
ORDER BY M.ID;

I hope you do not really have fields called name and tables called time, because these are reserved words.

EDIT re comment

SELECT m.Name, IIf(IsNull(ts.Hours),0,ts.Hours) AS Hrs
FROM Machine AS m 
LEFT JOIN (
     SELECT t.Name,t.Date,t.Hours 
     FROM [Time] AS T 
     WHERE T.Date=#10/25/2010#) AS ts 
ON m.Name = ts.Name;