ansaurus

Question

SQL Select Master Records and Display Number of Detail Records for Each

Answer 1

+3 A:

This may be useful

SELECT mt.ID, mt.NAME, mt.AGE, COUNT(d.MasterID) as [#Subjects]
FROM MasterTable mt
LEFT OUTER JOIN Detail d on mt.ID = d.ID
GROUP BY mt.ID, mt.NAME, mt.AGE
ORDER BY mt.ID

bobs 2010-10-21 20:00:33

You'll need to coalesce d.MasterID to zero before counting it, or it'll return NULL for master records with no children (like Joe).

djacobson 2010-10-21 20:05:57

Great. The keyword missing from the SQL I wrote was the word *OUTER*.

Batuta 2010-10-21 20:08:11

@djacobson - could you explain further? Right now, it is returning 0 if no detail record was found, as posted by @bobs.

Batuta 2010-10-21 20:09:42

Don't need the coalesce when using COUNT(column). You would need it for a SUM, as an example. If you use coalesce in this case, it would return 1 instead of 0.

bobs 2010-10-21 20:11:43

@bobs You're right. I was confused, because `COUNT(column)` skips NULLs, but it does indeed return 0 when it encounters them, not NULL. Good call!

djacobson 2010-10-21 20:23:36

Answer 2

+2 A:

select id, 
       name, 
       age, 
       ( select count(*) 
           from detail 
          where master.id = detail.id )  as record_count
  from master

syntax adjusted depending on what db you are using

Ray 2010-10-21 20:02:35

+1: Valid, though I'm not a fan of sub-selects

OMG Ponies 2010-10-21 20:05:42

I agree about sub-queries in general. But in this case, I don't. I just tried it on some large real tables. Both generated exactly the same query plan. To me, the sub-query version is more explicitly descriptive of what I am trying to do.

Ray 2010-10-21 20:16:48

ansaurus

tags:

views:

answers:

SQL Select Master Records and Display Number of Detail Records for Each

related questions