Count records in detail when query master

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Count records in detail when query master

Author

Topic

trellend
Starting Member

9 Posts

Posted - 2008-06-28 : 10:00:29

I have a master/detail table, similar to an invoice/invoice detail.

When I query the master, I want the number of detail records included in the query (a special query). I'm not sure of the syntax or how to do that. I'm thinking I should just store the number of records in the master table when done editing the detail.

The trick is that I want the query to return the count in the same query that returns the invoice header information, something like:

Select *, Count(Detail.*) Where Detail.InvoiceId=Invoice.InvoiceId From Invoice, Detail

Any ideas?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-28 : 10:12:36

[code]Select I.*, D.Det_Count
from Invoice I Join
(
Select InvoiceID, count(*) as Det_Count
From Detail
Group by InvoiceID
) D
on I.InvoiceID = D.InvoiceID[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

trellend
Starting Member

9 Posts

Posted - 2008-06-28 : 10:33:55

Learn something new everyday. Final SQL:


SELECT D.Det_Count, dbo.MeetingTraining.MeetingId, dbo.MeetingTraining.CoalitionId, dbo.MeetingTraining.MeetingType, 
               dbo.MeetingTraining.MeetingName, dbo.MeetingTraining.MeetingAgency, dbo.MeetingTraining.MeetingDate, dbo.MeetingTraining.MeetingPlace, 
               dbo.MeetingTraining.MeetingNotes, dbo.MeetingTraining.AttendBoard, dbo.MeetingTraining.AttendStaff, dbo.MeetingTraining.AttendMember, 
               dbo.MeetingTraining.AttendYouth, dbo.MeetingTraining.AttendCommunity, dbo.MeetingTraining.AttendOther, dbo.MeetingTraining.AttendHours, 
               dbo.MeetingTraining.TotalHours, dbo.MeetingTraining.InKindTotal, dbo.MeetingTraining.PreventionTarget, dbo.MeetingTraining.Impact, 
               MeetingTypes.MeetingTypeDesc AS Attend_Meeting
FROM  dbo.MeetingTraining INNER JOIN
               MeetingTypes ON dbo.MeetingTraining.MeetingType = MeetingTypes.MeetingTypeId INNER JOIN
                   (SELECT MeetingId, COUNT(*) AS Det_Count
                    FROM   dbo.MeetingMember
                    GROUP BY MeetingId) AS D ON D.MeetingId = dbo.MeetingTraining.MeetingId
WHERE (dbo.MeetingTraining.CoalitionId = '1') AND (dbo.MeetingTraining.MeetingName LIKE '')
ORDER BY dbo.MeetingTraining.MeetingType, dbo.MeetingTraining.MeetingDate

Thank you very much, back to work now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-28 : 14:01:09

quote:

Originally posted by trellend

Learn something new everyday. Final SQL:


SELECT D.Det_Count, dbo.MeetingTraining.MeetingId, dbo.MeetingTraining.CoalitionId, dbo.MeetingTraining.MeetingType, 
               dbo.MeetingTraining.MeetingName, dbo.MeetingTraining.MeetingAgency, dbo.MeetingTraining.MeetingDate, dbo.MeetingTraining.MeetingPlace, 
               dbo.MeetingTraining.MeetingNotes, dbo.MeetingTraining.AttendBoard, dbo.MeetingTraining.AttendStaff, dbo.MeetingTraining.AttendMember, 
               dbo.MeetingTraining.AttendYouth, dbo.MeetingTraining.AttendCommunity, dbo.MeetingTraining.AttendOther, dbo.MeetingTraining.AttendHours, 
               dbo.MeetingTraining.TotalHours, dbo.MeetingTraining.InKindTotal, dbo.MeetingTraining.PreventionTarget, dbo.MeetingTraining.Impact, 
               MeetingTypes.MeetingTypeDesc AS Attend_Meeting
FROM  dbo.MeetingTraining INNER JOIN
               MeetingTypes ON dbo.MeetingTraining.MeetingType = MeetingTypes.MeetingTypeId INNER JOIN
                   (SELECT MeetingId, COUNT(*) AS Det_Count
                    FROM   dbo.MeetingMember
                    GROUP BY MeetingId) AS D ON D.MeetingId = dbo.MeetingTraining.MeetingId
WHERE (dbo.MeetingTraining.CoalitionId = '1') AND (dbo.MeetingTraining.MeetingName LIKE '')
ORDER BY dbo.MeetingTraining.MeetingType, dbo.MeetingTraining.MeetingDate

Thank you very much, back to work now.

Didnt understand why you're using LIKE ''? cant you use = instead?

trellend
Starting Member

9 Posts

Posted - 2008-06-28 : 22:10:27

This is "auto generated" by looking at the choices the user made. Since meeting name is text, it defaults to using like. Since no text was entered, it skips adding the %'s to the Query. Default behaviour.

Were it a numerical field, it would have choosen '='

I don't want people accidentally pulling every record in the database...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-29 : 02:33:26

quote:
Originally posted by trellend

This is "auto generated" by looking at the choices the user made. Since meeting name is text, it defaults to using like. Since no text was entered, it skips adding the %'s to the Query. Default behaviour.

Were it a numerical field, it would have choosen '='

I don't want people accidentally pulling every record in the database...

Ok. Thanks for the explanation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources