Need efficient query for total count from related table

[sfatz]

For the sake of this question, I have a database with two related tables in it.

The primary table contains project information with the primary key being 'ID'.

The secondary table has costs associated with the ID.

Each project may have many associated costs, but not all projects need to have a cost (New projects will have no costs).

I need an efficient query that will provide a count of the associated costs for each project ID. The key word here is efficient since the tables are large.

Thanks in advance for any help that you can provide

 

[PHV]

count of the associated costs for each project ID
SELECT A.ID, Count(B.ID) As CountOfCosts
FROM tblProjects AS A LEFT JOIN tblCosts AS B ON A.ID = B.ID
GROUP BY A.ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kjv1611]

Since you're talking "Count", and I'm assuming you're just creating the queries in Access, then could you not create a query, hit the "AutoSum" looking button on the toolbar, change the "Group By" to "Count" in the Costs column, and only have that as well as the "Project name" field, and leave it as "Group By"?

Will that not work?

 

[sfatz]

kjv1611: Yes this is in Access, and I suppose that I could create a query, and then use that where necessary. However, this is for a very large (complex?) application that has both Access and SQL back ends. At the risk of aging myself, a long time ago I found that working directly with SQL statements tended to make things easier in the long run. Also, if one knows the SQL, then a query can be created on the fly and thus we don't muddy up the database with extraneous objects.

PHV: Let me try this and I'll get back to you. Though something tells me I tried something like this awhile back.