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?

score 4 · Accepted Answer · answered Jul 17 '13 at 09:00

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

Thank you! I didn't knew that I can put many conditions on the join. :) — David White, Jul 17 '13 at 09:12

score -1 · Answer 2 · answered Jul 17 '13 at 09:11

-1

it give you diff machine name viz routingcode and count

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

answered Jul 17 '13 at 09:11

uttam patel

43
3
8

Apart from the addition of a pointless `distinct` and column rearrangement this is the same as what is in the original question. – Martin Smith Jul 17 '13 at 09:12

SQL Server left joining

2 Answers2

Linked

Related