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?
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.
Joined: Feb 22, 2011
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.