Search code examples
sqlgroup-bysap-ase

Unexpected results when trying to left join

I have this SQL statement which works fine..

SELECT result=count(j.client_id), j.client_id 
FROM spp_job j  
GROUP BY j.client_id 
ORDER BY  count(j.client_id) DESC

but failed when I use LEFT JOIN

SELECT result=count(j.client_id), c.name 
FROM spp_job j 
LEFT JOIN spp_client c ON j.client_id = c.id  
GROUP BY j.client_id 
ORDER BY count(j.client_id) DESC

I don't know what went wrong. I want to show the client name instead of their id. The second SQL give me all client records.

Solution

  • Since you are listing c.name with the left join, you need to Group By c.name instead j.client_id

    SELECT result=count(j.client_id), c.name 
FROM spp_job j LEFT JOIN
    spp_client c ON j.client_id = c.id  
GROUP BY c.name 
ORDER BY result DESC

    UPDATE: Since name is not unique (as @lc. said) you may need to use group by both by id and name for accurate results. 

    GROUP BY j.client_id, c.name 
ORDER BY result DESC