Count occurrences of a company in 3 separate tables

Sketchin

Registered User.
Local time
Today, 09:15
Joined
Dec 20, 2011
Messages
577
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:
 

Attachments

  • Screenshot 2014-12-18 15.33.27.jpg
    Screenshot 2014-12-18 15.33.27.jpg
    94.5 KB · Views: 85
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:

Code:
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
 
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 . . .
Code:
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.
 

Users who are viewing this thread

Back
Top Bottom