aspose file tools*
The moose likes JDBC and the fly likes Single query that counts children including zero elements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Single query that counts children including zero elements" Watch "Single query that counts children including zero elements" New topic
Author

Single query that counts children including zero elements

Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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?


My Blog: Down Home Country Coding with Scott Selikoff
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18657
    
    8

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

Joined: Feb 22, 2011
Posts: 75
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.
 
jQuery in Action, 2nd edition
 
subject: Single query that counts children including zero elements