Master detail sql query

I have two tables Master and Detail that have a master detail relationship.

How would I write a query that returns all the columns from the Master table with a count of the records in the Detail Table.
soozhAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
select *, (select count(*) from DetailTable where DetailTable.MasterID = MasterTable.ID) from masterTable
0
 
aelliso3Commented:
what's the field name that you are using to join the tables?
0
 
LowfatspreadCommented:
select m.*,coalesce(d.detailcount,0) as details
from mastertable
left outer join (select masterid,count(*) as detailcount
                         from detailtable
                         group by masterid
                      ) as d
on m.masterid=d.masterid
order by m.masterid
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
halfbloodprinceConnect With a Mentor Commented:
Considering Orders as Master table and OrderItemDetails as the Details table,this is giving me the items ordered in each order

Select  OrderNumber,
            Count(ItemID) as NoOfItems
From      Orders
Inner Join OrderItemDetails
On  OrderItemDetails.OrderID = Orders.OrderID
Group By OrderNumber
0
 
soozhAuthor Commented:
thanks for the response... there seems to be two methods... one offered by cyberkiwi and the other by Lowfatspread/halfbloodprince.   (wish i could give the points based upon your nicks!)

Anyway cyberkiwi is the simplest so what is the advantage(s) with the other approach using joins?
0
 
LowfatspreadCommented:
the inner join will only give results for masters that have details attached,,

the outer and subquery on select will give results for all master rows...

the difference between the outer and subquery approach may have  a bearing on the access plan that is chosen by the databas engine that you are using.. you have to check if performance is a problem/potential problem but in general there isn't a difference
using the join syntax can make maintenance easier , but again thats very "style" dependant
0
 
cyberkiwiCommented:
Well, to say the least, hbp's code needs a fix from inner to LEFT join...
But if that is fixed easily, it still performs better than lfs's unnecessary coalesce and join after subquery...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.