Left join?

[lurtnowski]

I have this

SELECT power_distributors.power_distributor_id, title, manufacturer, model, created_date, enabled, number_of_outlets, number_of_devices 
FROM power_distributors 
LEFT JOIN 
(SELECT power_distributor_id, COUNT(device_id) AS number_of_devices 
FROM devices) AS tpr 
ON tpr.power_distributor_id = power_distributors.power_distributor_id 
WHERE type = 1
 LIMIT 0, 10

result,

When I look in the devices table

There are a total of four devices, but only 2 with a power_distributor_id of 1
I thought the JOIN would only count the two, not all the devices. I cant put a WHERE clause in the joined statement, so how do I make it only count 2?

[DaveMaxwell]

You’d need to group by power_distributor_id

SELECT power_distributors.power_distributor_id
     , title
     , manufacturer
     , model
     , created_date
     , enabled
     , number_of_outlets
     , number_of_devices 
  FROM power_distributors 
  LEFT JOIN (SELECT power_distributor_id
                  , COUNT(device_id) AS number_of_devices 
               FROM devices
              GROUP BY power_distributor_id) AS tpr ON tpr.power_distributor_id = power_distributors.power_distributor_id 
 WHERE type = 1 
 LIMIT 0, 10

[lurtnowski]

thanks, so the group by thing only groups 1 number even if there ar more?
for some reason I thought it would return
1,1,1,2,2,2,3,3,3,4,4,4,5,5,5

[DaveMaxwell]

Your original code counted ALL devices, regardless of the power_distribution_id.

_ power_distribution_id _|_ count _
++++++++++++++++++++++++++++++++++++
          1              |   4
          1              |   4
          2              |   4
          2              |   4

The group by counts per power_distribution_id so it returns

_ power_distribution_id _|_ count _
++++++++++++++++++++++++++++++++++++
          1              |   2
          2              |   2

[m_hutley]

Which is not to say that the query should.
In the case of a LEFT JOIN, you should get all of the power_distributors rows where type is 1, but any that have no device count should have a NULL for the number_of_devices column.

Without knowing the unfiltered contents of your power_distributors table, we can’t speculate what the query should return data-wise. You’d have to look at how many of your power_distributors have type 1.