?
Solved

One Query Across Multiple Database, Using Multiple Tables to Generate SubTotals and Totals

Posted on 2007-08-08
5
Medium Priority
?
1,211 Views
Last Modified: 2008-03-20
I have "N" databases on a given server. Each database contains two tables from which I need to gather data from to generate a report with totals. The key here is that I need ONE SQL query (not stored procedure) to return the proper data for display in a tabular type report.

For the sake of discussion, let's say that each database represents a customer. So we have the following:
Database X
Database Y
Database Z

Each of the databases above have the following two tables:
TableA
TableB

TableA contains site information for that customer and in particular the following columns of interest for this discussion are:
URLPath = URL to customer site
SiteID = unique ID representing customer site

TableB contains the configuration of all the customer sites, like which components are viewable on that site. In particular the columns of interest are:
SiteID = The ID of the site this component belongs to
ComponentName = The name of the component
Enabled = Whether or not the component is enabled on that site

So, within a given database to retrieve the list of components that are enabled on each site, the query is simple

SELECT TableA.URLPath, TableB.ComponentName
FROM TableA
INNER JOIN TableB
ON TableB.SiteID = TableA.SiteID AND TableB.Enabled = 'Y'
ORDER BY TableB.ComponentName

However, if that was all I was looking for I would not have submitted this inquiry. What I am interested in is a result set using ONE SQL query that would give me the data such that the following tablular report can be generated. Notice that the report goes across databases on the server and generates subtotals for the components within each database and totals across the databases. That is the challenge!! Good luck!!

Customer Name: <Database X Name>

URL            ComponentX                          ComponentY                          ComponentZ
www.go123.com      1            0            1
www.go234.com      0            1            1
www.go456.com      0            0            1
SubTotal                            1            1            3

Customer Name: <Database Y Name>

URL            ComponentX                           ComponentY                          ComponentZ
www.xy123.com      1            0            0
www.xy234.com      1            1            1
www.xy456.com      1            1            1
SubTotal                           3            2            2

Customer Name: <Database Z Name>

URL            ComponentX                           ComponentY                          ComponentZ
www.ab123.com      0            0            1
www.ab234.com      1            0            0
www.ab456.com      1            0            1
SubTotal                           2            0            2

TOTAL            6            3            7
0
Comment
Question by:Bizzuka IT
  • 2
4 Comments
 
LVL 44

Accepted Solution

by:
GRayL earned 1000 total points
ID: 19655619
Use a UNION ALL query for each site.  Each SELECT statement past the first would use the format:

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]}
    FROM tableexpression [, ...] [IN externaldatabase]
    [WHERE... ]
    [GROUP BY... ]
    [HAVING... ]
    [ORDER BY... ]
    [WITH OWNERACCESS OPTION]

Here, the externaldatabase would define the path and name of each of the mdb's from which you wanted to extract the data.  Each SELECT statement would in fact be a INNER JOIN on the two tables of interest.
0
 
LVL 25

Expert Comment

by:jogos
ID: 19655696
Create a view with UNION that gets the result-base you want. The report with grouping ...  you do on this view.

CREATE VIEW vwMyCrossDBView
AS
SELECT TableA.URLPath, TableB.ComponentName
FROM DATABASEX.DBO.TableA as tableA
INNER JOIN DATABASEX.DBO.TableB as tableB.....
UNION ALL
SELECT TableA.URLPath, TableB.ComponentName
FROM DATABASEY.DBO.TableA as tableA
INNER JOIN DATABASEZ.DBO.TableB as tableB.....
-- an select on the view
select URLPath,count(*)
from vwMyCrossDBView
group by URLPath
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 1000 total points
ID: 19655798
It also can be done without the view, but readability will be less

select crostab.URLPATH, count(*) from
-- in case of a real table you select from the result of onother query
(SELECT TableA.URLPath, TableB.ComponentName
FROM DATABASEX.DBO.TableA as tableA
INNER JOIN DATABASEX.DBO.TableB as tableB.....
UNION ALL
SELECT TableA.URLPath, TableB.ComponentName
FROM DATABASEY.DBO.TableA as tableA
INNER JOIN DATABASEZ.DBO.TableB as tableB.....
)
AS crosstab -- give it an alias
group by URLPath
0
 
LVL 1

Expert Comment

by:Computer101
ID: 21176962
Forced accept.

Computer101
EE Admin
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question