Hmmm. I thought I had this problem licked, but now I can't seem to get the result I'm after in this scenario:
I've got two tables, 'registrars' and 'domains' - it's a 1:many relationship. RegisterName and DomainName are both unique fields on their respective tables, amd the foreign key 'DomainRegistrarID' sits on the domains table to make the link to the registrars table. All I want to do is to show a list of Registrar names, and the number of Domain names associated with each.
The problem I have is this: not all domains have been assigned (or linked to) a registrar yet, and some of the Registrars on my registrars table have not been assigned/linked to any domains. But, all the Registrars that have no domains linked to them are showing a count of 1 in the above example when they should (IMHO) be showing 0.
$sql = "SELECT RegistrarName, DomainName, Count(*) AS count FROM registrars
LEFT JOIN domains ON RegistrarID=DomainRegistrarID
GROUP BY RegistrarName
ORDER BY RegistrarName
echo "$RegistrarName, $count";
More info: if a domain has not yet been linked to a registrar, then the DomainRegistrarID field is 0. RegistrarID values on the registrar table start from 1.
So obviously I've got some something wrong in my logic or my syntax - but what? Any takers prepared to point out the error of my ways? I've got a headache.