UNIONS - selecting one record only

From: EvaM <knowaczNoSpam_at_usa.net>
Date: Mon, 23 Nov 1998 15:41:34 -0500
Message-ID: <3659C87E.AD_at_usa.net>

Hello All!
I have little problem with unions. What I'm trying to do is to create a table with one unique row for each ID number. The table has totals combined from two financial tables.
The problem is that the common value I'm trying to get from my union which is the ID number, is called differently in both tables and finally I'm not able to select uniqly one row.
Example:

  SELECT GIFT.DONOR_ID id_number,

         sum(GIFT.GIFT_AMOUNT) total
    FROM GIFT
   WHERE GIFT.DONOR_ID = '0000037437' GROUP BY GIFT.DONOR_ID
   UNION
  SELECT MATCHING.MATCH_ID id_number,

         sum(MATCHING.AMOUNT) total
    FROM MATCHING
   WHERE MATCHING.MATCH_ID = '0000037437' GROUP BY MATCHING.MATCH_ID" The above will return something like:

00001234     50.00
00001234     87.00

While if the ID identifiers in both table would have the same name (ex. GIFT.ID and MATCHING.ID), the union would return one row only.

What I'm doing now is creating temp table which holds data from MATCHING table and then updates the first one, but I don't think this is teh best solution.

Do you have any clue about how to make Union work to get around this problem?

TIA for your greatly appreciated help.
Greetings,
Eva Maciasz
Systems Dept.
McGill University, Montreal, CA Received on Mon Nov 23 1998 - 21:41:34 CET