Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Single query that counts children including zero elements

 
Scott Selikoff
author
Saloon Keeper
Posts: 4015
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'd like to join two tables (parent/child relationship) and show the items name from the Table A along with a count(*) of the number of matching items in table B. The result should look something like this:

102, WidgetA, 10 --> Indicating there are 10 records in table B
105, WidgetB, 0 --> Indicating there are 0 records in table B
394, WidgetC, 4 --> Indicating there are 4 records in table B

If 0-items (such as WidgetB) weren't included I could perform the following query:



But the query above will drop WidgetB records since there are no matching children. I could change the "INNER JOIN" to "LEFT OUTER JOIN", but then my result shows:

105, WidgetB, 1

Which is incorrect, as WidgetB does not have any children. Any suggestions?
 
Paul Clapham
Sheriff
Posts: 21152
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Include a non-null column from your "child" table. If the join selects zero records from "child" then that column will be null in the query result, otherwise if it selects some records then it won't be null. Obviously I've left out a lot of details (because I haven't thought it all the way through) but hopefully you can make that into a workable solution.
 
Sudheer Bhat
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:Include a non-null column from your "child" table.

As Paul has stated, if you include a column name in the count() function then it shows the count of the non null values for that particular column.Here is a link from w3schools about the count function.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic