Count the children

[george]

I have two tables, and I am gathering the number of children records for each parent record:

SELECT p.propid, p.VenuName, p.instruc, p.POC_Last, p.eventStage, p.email,COUNT(c.eventID) AS RemainingClassesFROM proposal pLEFT JOIN ClassesTaught c ON p.propid = c.eventID WHERE c.submitted='No'GROUP BY c.eventID;

But if there are no records in the second table, I still want data from the first table. I suspect that I have to have corresponding data in my second table, since I am using a group by clause and a count() to populate a column in the result set. Is there another way I can work this?

[justsomeguy]

Yeah when you're grouping by the table on the "right" the left join might not work so well. Try to group instead on p.propid.

[george]

Thank you. I still am not seeing the data on the left when there is no data on the right. I want to list all the adults on the block, and then in the next column provide the number of kids per adult. Not all the adults have kids, I still need to list them.

[justsomeguy]

My bad, you're looking for a LEFT OUTER JOIN, not an inner join.

[george]

I tried that. I am still only getting records on the left which have records on the right to count. It is as though the LEFT OUTER JOIN was being over ridden by the COUNT(right.table) and the group by right table column. Maybe I need seperate this into two queries. The first, just employing the outer join, and the second to count the children.

[justsomeguy]

Maybe so. But definately don't group over the right column. It doesn't make sense to do a left outer join to get everything on the left and then group by something on the right that might not be there. Group by the joined column on the left instead, they're the same right?

[george]

I figured it out. What I was trying to get at was a full outter join, and mysql does not support it. The work around is at : Simulate a full outter join in MySQL