Nick Freeman Nick Freeman - 6 months ago 21
MySQL Question

MySQL Not getting rows with zero from count and outer join

For an online game, I have two tables one which lists all breeds(horsebreeds) and the other lists each individual animal(horses) and I am looking to list out the total number of animals of each breed which are alive and owned by a member (owner != 0). I am getting the results for rows which have values but not those which would be a count of zero. I know i need an outer join for this but I must not be structuring it right?

Here is the query:

$breedquery = mysql_query("SELECT COUNT(H.breed) AS totalbreed, H.breed FROM horses H RIGHT JOIN horsebreeds B ON H.breed = B.breedname WHERE H.owner != 0 GROUP BY H.breed ORDER BY totalbreed DESC");


here is a snapshot of the results, as you can see it works to return those with 1 or more but omits breeds with zero

snapshot of results

Thank you for any help with the issue!

Answer

LEFT JOIN is generally preferable to RIGHT JOIN -- ` left join keeps all rows in the first table which is usually simpler to follow.

SELECT COUNT(H.breed) AS totalbreed, B.breedname
FROM horsebreeds B LEFT JOIN
     horses H   
     ON H.breed = B.breedname AND H.owner <> 0
GROUP BY B.breedname
ORDER BY totalbreed DESC;

Then you need to make two changes:

  1. Put the filter on owner in the ON clause. Otherwise, you are turning the outer join into an inner join.
  2. Use the columns from horsebreeds for the GROUP BY and SELECT.