>In my web application I have 3 tables, EducationalActivities, EducationProviders, and EducationalActivitiesEducationProvidersXRef. As the names may tell you, the XRef is a cross reference table between the Activities and the Providers. I need to display the information as follows:
>
>
> _____ Provider #1
>
> _____ Provider #2
>
> _____ Provider #3
>
>With the underscores being text boxes to hold the count for each Provider.
>
>How can I do this?
Where is your data? On SQL Server, or a similar DBMS?
If that's the case, you may use (as the CommandText) the following expression:
SELECT prov.ProviderId, count(xref.ProviderId) as Count
FROM EducationProviders prov
LEFT JOIN EducationalActivitiesEducationProvidersXRef xref
ON prov.ProviderId xref.ProviderId
GROUP BY ProviderId
That will return all Providers (even those with Count=0)
If you don't want the zeros, replace the LEFT JOIN with an INNER JOIN
Hope this helps
Jose.