Re: SQL query for joining results of queries

On 6 Aug 2002 00:43:20 -0700, srigurunath_at_yahoo.com (Ecap) wrote:

>Hi,
> This may be a simple question (?)

Not really.

> 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?

Yes

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

SELECT * FROM (
(SELECT C, Field1 , ..., FROM T_1 WHERE ... ) FULL OUTER NATURAL JOIN
(
(SELECT C, Field2, ..., FROM T_2 WHERE ... ) FULL OUTER NATURAL JOIN
(
(SELECT C, Field3, ..., FROM T_3 WHERE ... ) FULL OUTER NATURAL JOIN
...
)))

Beware: Fieldnames in the queries must be different, except for C. If this is not acceptable, you must add a join condition rather than making it NATURAL.

-- 
Nis Jørgensen
Amsterdam

Please include only relevant quotes, and reply below the quoted text. Thanks