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");
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:
ONclause. Otherwise, you are turning the outer join into an inner join.