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.
soozhCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aelliso3Commented:
what's the field name that you are using to join the tables?
0
cyberkiwiCommented:
select *, (select count(*) from DetailTable where DetailTable.MasterID = MasterTable.ID) from masterTable
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

halfbloodprinceCommented:
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
soozhCEOAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.