Re: SQL query for joining results of queries

From: Tokunaga T. <tonkuma_at_jp.ibm.com>
Date: 7 Aug 2002 05:35:24 -0700
Message-ID: <8156d9ae.0208070435.fdc99f6_at_posting.google.com>

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

If NATURAL keyword is not supported by your DBMS, you also need to add join conditions.
Note: You must use coalesce functions in second and subsequent join conditions.
Like this:
SELECT coalesce(t1.c,t2.c,t3.c)

      ,"Count_in_T_1"
      ,"Count_in_T_2"
      ,"Count_in_T_3"

FROM
  (SELECT C, count(*) as "Count_in_T_1" FROM T_1 GROUP BY C) AS t1   FULL OUTER JOIN
  (SELECT C, count(*) as "Count_in_T_2" FROM T_2 GROUP BY C) AS t2     ON t2.c = t1.c
  FULL OUTER JOIN
  (SELECT C, count(*) as "Count_in_T_3" FROM T_3 GROUP BY C) AS t3     ON t3.c = coalesce(t1.c, t2.c) Received on Wed Aug 07 2002 - 14:35:24 CEST