[thelist] Help Combining SQL Results

[Jay Turley]

Hi all-

I have three tables: A , B, and C, each with primary keys "id"

C.id is used as a foreign key in A and B

I have a SELECT statement similar to the following:

SELECT
    a.id, a.value, a.c_fk_id, count(*) as num_entries
FROM
    A a
GROUP BY
    a.id, a.value
UNION
SELECT
    b.id, b.value, b.c_fk_id, count(*) as num_entries
FROM
    B b
GROUP BY
    b.id, b.value

While this works to get the data of interest, the problem with is that
for any given C.id, I will get two rows returned: one from the A
select portion of the query, and another from the B select portion.

What might be the best way to be able to pull the data out so that for
any given C.id, I will only get one row, with the combined totals from
both A and B.

Any help greatly appreciated.

Thanks!

-Jay Turley