SQL Server left joining

count, group-by, join, sql-server, subquery

Solution

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

Problem

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?

Original source