Обсуждение: Cross joining table with itself

I've been stumped trying to solve this problem via SQL. I have a table ID,
member1, member2, member3. In any row, any member field may have a value
or be null. I want to tally up how many times each member appears in the
table as a whole. For example, if two entries were ('a', '', 'c') and
('b', 'c', ''), I'd like my final result to be:

a, 1
b, 1
c, 2

Is this feasible? Thank you for any help.

On Sun, Sep 21, 2003 at 14:38:23 +0000, Bob Hutzel <tidal7@myrealbox.com> wrote:
> I've been stumped trying to solve this problem via SQL. I have a table ID,
> member1, member2, member3. In any row, any member field may have a value
> or be null. I want to tally up how many times each member appears in the
> table as a whole. For example, if two entries were ('a', '', 'c') and
> ('b', 'c', ''), I'd like my final result to be:
> 
> a, 1
> b, 1
> c, 2
> 
> Is this feasible? Thank you for any help.

You can 'union all' selects of each of the three columns where the appropiate
member value is not null and use group by and count on the result.