CODEWITHSUNDEEP
mysqlgroup-byleft-join
zztop
zztop

Reputation: 781

MYSQL query LEFT JOIN and GROUP BY

I have a query that is pulling from a table and left joining on another table. If I remove the LEFT JOIN, it returns ten records due to a LIMIT 10 in the query. When I add a LEFT JOIN, it is only returning the one record that has a match in the joined table.

How can I get it to return all ten records include the column from the other table if there is a match but if there is no match leave the column blank?

Here are the two versions of the query, one without and one with LEFT JOIN:

Version 1: Returns 10 records:

$sql = "SELECT d.first,d.last,d.descript,d.profilepic,d.userid,d.id   
FROM demo `d`";
$sql.=" LIMIT 10";

Version 1: Returns 1 Record. Of the 10 records, only has a match in points

$sql = "SELECT d.first,d.last,d.descript,d.profilepic,d.userid,d.id,SUM(p.points) AS points 
FROM demo `d` 
LEFT JOIN points `p` 
ON p.userid = d.userid";
$sql.=" LIMIT 10";

Thanks for any suggestions.

Upvotes: 0

Views: 166

Answers (1)

Fahmi
Fahmi

Reputation: 37473

You need to add group by 

SELECT d.first,d.last,d.descript,d.profilepic,d.userid,d.id,SUM(p.points) AS points 
FROM demo `d` 
LEFT JOIN points `p` 
ON p.userid = d.userid
group by d.id

Upvotes: 1

Related Questions