Count occurrences of a company in 3 separate tables

[Sketchin]

Hi All,

I am trying to count the number of times a client has engaged with our company. I have a company table, a reservation table, a rapid prototyping table and a project table. There is a one to many from the company table to each of the other 3 tables as can be seen in the attached picture.

Is there an easy way, in a single query, to list unique company names that exist in 1 or all of the 3 tables?

There must be a way....:banghead:

 

[plog]

Technically, no. Practically, yes. Use a UNION query (http://www.techonthenet.com/sql/union.php), it will bring together the results of 3 queries into one.

Here's the SQL you will need:

SELECT CompanyName
FROM tblCompanies INNER JOIN tblRapidPrototyping ON tblCompanies.CompanyID = tblRapidPrototyping.CompanyID
GROUP BY CompanyName
UNION
SELECT CompanyName
FROM tblCompanies INNER JOIN tblProject ON tblCompanies.CompanyID = tblProject.CompanyID
GROUP BY CompanyName
UNION
SELECT CompanyName
FROM tblCompanies INNER JOIN tblReservations ON tblCompanies.CompanyID = tblReservations.CompanyID
GROUP BY CompanyName

 

[MarkK]

You can also run a count on each child table, union the counts together and treat that as a subquery, and Sum the subquery, like . . .

SELECT Sum(SubCount) AS TotalCount FROM 
(
   SELECT Count(*) As SubCount FROM tblProject WHERE CompanyID = 12
   UNION ALL
   SELECT Count(*) FROM tblReservation WHERE CompanyID = 12
   UNION ALL
   SELECT Count(*) FROM tblPrototype WHERE CompanyID = 12
)

. . . and that'd be pretty easy to parameterize for CompanyID.