Hi. I'm looking at a problem and I can't find any solution short of
coding.

I have a pool of, say, 1000 PINS. I have 7 tables (buckets). Any of
the 1000 PINS can be in 0, 1, 2, or... or all 7 buckets. So let's say
that

-bucket A has 100 PINS
-bucket B has 300 PINS
-bucket C has 600 PINS
-bucket D has 200 PINS
-bucket E has 500 PINS
-bucket F has 350 PINS
-bucket G has 700 PINS

I need to know, for each PIN, the number of buckets (tables) it
belongs to, and which ones, i.e:
- PIN 1 belongs to A, C, D, so it belongs to 3 buckets
- PIN 2 belongs to A, C, D, F, so it belongs to 4 buckets
- PIN 3 belongs to A, so it belongs to 1 bucket
- PIN 4 belongs to A, B, C, D, G, so it belongs to 5 buckets
- PIN 5 belongs to ..., so it belongs to 0 buckets
etc, etc

What would be the simplest way to achieve that, please ?

Thank you very much
Alex.

"Radu" <cuca_macaii2000 (AT) yahoo (DOT) com> wrote

Why would you use seven tables to represent this information? There may be a
valid reason but without at least more info on keys I don't know. This looks
suspiciously like homework so rather than complete a solution for you I'll
suggest that you think about a UNION or a JOIN.

--
David Portas

SELECT PIN, COUNT(distinct Bucket)
FROM (SELECT PIN, 'A' as Bucket FROM BucketA
UNION ALL
SELECT PIN, 'B' FROM BucketB
UNION ALL
SELECT PIN, 'C' FROM BucketC
UNION ALL
SELECT PIN, 'D' FROM BucketD
UNION ALL
SELECT PIN, 'E' FROM BucketE
UNION ALL
SELECT PIN, 'F' FROM BucketF
UNION ALL
SELECT PIN, 'G' FROM BucketG)
GROUP BY PIN

If a PIN can only appear once in each bucket then you could do without
the DISTINCT in the COUNT. In fact you could do without the Bucket
column all together in that case and simply use COUNT(*). But this
should work regardless of whether PIN is unique with a bucket.

Roy Harvey
Beacon Falls, CT

On Thu, 01 Nov 2007 21:00:37 -0000, Radu <cuca_macaii2000 (AT) yahoo (DOT) com>
wrote:

CREATE TABLE Buckets
(bucket_name CHAR(1) NOT NULL
CHECK (bucket_name IN ('A','B','C','D','E','F','G')),
pin_nbr INTEGER NOT NULL
CHECK(pin_nbr BETWEEN 1 AND 1000),
PRIMARY KEY (bucket_name, pin_nbr));

SELECT pin_nbr, COUNT(*) AS bucket_cnt
FROM Buckets
GROUP BY pin_nbr;

Thank you all for spending the time answering. Problem solved, and I
have learned something.

Thanks again ! Alex.

As an aside, if you want to be sure that each pin is in one and only
one bucket, then add an overlapping UNIQUE constraint.

CREATE TABLE Buckets
(bucket_name CHAR(1) NOT NULL
CHECK (bucket_name IN ('A','B','C','D','E','F','G')),
pin_nbr INTEGER NOT NULL UNIQUE
CHECK(pin_nbr BETWEEN 1 AND 1000),
PRIMARY KEY (bucket_name, pin_nbr));