SQL Server left joining

Question

I'm trying to make left join in one query, but it seems that I'm wrong somewhere.

table machines
-------------- 
machineID
FaNo
Barcode
RoutingCode
Name


table log
-------------
logID
lineBarcode
machineBarcode

In the log table there are records on the machines and the lines. On one line there can be many different machines and machines from the same type.
The machine type is routingCode, so I'm interested in selecting all the machines in the line and group them. Only machines with different routingCode should display separately, and I want to get the count of the machines of every type.
This is done this way.

SELECT routingcode, name, count(1)
FROM machines 
JOIN log ON log.machinebarcode = machines.barcode
WHERE log.linebarcode = 100000000001
GROUP BY routingcode, name

Okay everything runs smoothly, but this way I get only machines which are related in log table and have record according to linebarcode.
I thinked that if I LEFT JOIN the log table I will get all the machines from the machines table and display them and of course only machines which are found in log table will have proper count, but no.
Where am I mistaking and how to find a proper workaround?

Answer 1

You need to put the condition on log into the on clause not the where. Non matching rows preserved by the left outer join will be null extended for all columns in log.

Rows with NULL for log.linebarcode will be removed again if the condition is in the where.

Also instead of COUNT(1) you need to count a column from log that won't be NULL

SELECT routingcode,
       name,
       count(log.linebarcode)
FROM   machines
       LEFT JOIN log
         ON log.machinebarcode = machines.barcode
            AND log.linebarcode = 100000000001
GROUP  BY routingcode,
          name