This week's book giveaway is in the Android forum.
We're giving away four copies of Head First Android and have Dawn & David Griffiths on-line!
See this thread for details.
The moose likes JDBC and Relational Databases 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 Head First Android this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
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: 3740
    
  10

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?


[OCA 8 Book] [Blog]
Paul Clapham
Sheriff

Joined: Oct 14, 2005
Posts: 19687
    
  10

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Single query that counts children including zero elements
 
It's not a secret anymore!