LEFT JOIN does not show all rows for the table on the left

Question

Consider this query:

SELECT s.*, COUNT( ssh_logs.id ) AS ssh_count FROM servers s 
LEFT JOIN logs ssh_logs ON s.ip_address = ssh_logs.server_ip

It seems to me that it LEFT JOINshows all the rows in the left table, regardless of whether there is a match for the condition ON.

SELECT s.* FROM servers s

It returns 12 records, while the first query returns only 1 where the IP addresses correspond.

So, how do I get the first query to display all the rows in the server table along with the associated table data?

+3

Hydera Jun 09 '11 at 11:22

1 answer

Johan · Accepted Answer · 2011-06-09T11:26:02+0000

The summary function count () collapses all the lines into one.
Make group byto see the invoice on the ip address.

SELECT s.*, COUNT(ssh_logs.id) AS ssh_count FROM servers s  
LEFT JOIN logs ssh_logs ON s.ip_address = ssh_logs.server_ip 
GROUP BY s.ip_address

, server.ip_address - ( ).
ip_addresses, ip_addresses , .

, , , , ip_address .