SQL query for joining results of queries

From: Ecap <srigurunath_at_yahoo.com>
Date: 6 Aug 2002 00:43:20 -0700
Message-ID: <ccbfa695.0208052343.2e6b9a09@posting.google.com>

Hi,
This may be a simple question (?)
I want to perform the following operation (below) on a set of rdbms tables. Can someone please guide me on how to do this using one SQL query?

Descripton of problem: There are N rdbms tables (T_1, T_2,...) with the same schema but containing different data. I want to execute the same Query Q on each table, T_x, that returns results containing a column name (C), and then join the results on C. That is, one SQL query (nested or other) is required to execute Q for each of the tables T_x and return the result as one table.

For example, if there are 3 tables, and the query, Q, to execute on each of them is "Select C, count(*) as "Count_in_T_x" from T_x group by C".

If the individual results are like this:
(from query Q on T_1)

C Count_in_T_1

c1 100
c2 500

(from query Q on T_2)

C Count_in_T_2

c1 10
c3 50

(from query Q on T_3)

C Count_in_T_3

c3 1
c2 5

The desired result from the single query I'm looking for should be as follows:

C Count_in_T_1 Count_in_T_2 Count_in_T_3

c1     100            10             0  
c2     500             0             5
c3       0            50             1

best regards,
Ecap Received on Tue Aug 06 2002 - 02:43:20 CDT