Jump to content

Count the children


george
 Share

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...