I have this:
"SELECT *, COUNT(replies.record_ID) " +
"AS number_replies " +
"FROM reports,replies " +
"WHERE replies.record_ID=reports.record_ID " +
"GROUP BY reports.record_ID";
The problem with this is if the replies table has zero records, I don't get my record from the reports table. What would work is a subselect from within COUNT. Something like this.
SELECT *, COUNT(
SELECT * FROM replies
This, of course, is illegal. My situation is that I have a one to many relationship between two tables. The 'many' table has a foreign key to the unique primary key in the 'one' table. I want to select from the 'one' table, then count the number of entries in 'many' that match the primary key.
, count(replies.record_ID) as number_replies