SQL puzzle (for entertainment only)
{ I'm doing the casting for Sesame Street (tm)....
I have a table that lists all the nice letters, another table that
lists all the bold letters, & another table that lists all the tall
letters:-
TALL NICE BOLD
name name name
____ ____ ____
A B K
B C L
C K M
K L N
G M P
H D Q
I E G
J F H
I
J
My producer demands a quick summary of how many letters are tall and
nice and bold; how many are tall and nice but not bold, how many are
nice but neither tall nor bold, etc.
So how to calculate these numbers? One way would be a series of seven
SQL statements:
(1) select count(*)
from bold, tall, nice
where bold.name = nice.name
and bold.name = tall.name ; - number who are tall and
bold and nice.
(2) select count(*)
from bold
where name not in ( select name from nice )
and name not in ( select name from tall ) ; - number who are bold but
neither tall nor nice.
etc.
But I am secretly convinced that he is going to spring more binary
attributes on me - which letters are fuzzy, which are rude, which
are cheap, etc - and then ask for the number of letters in each of
resulting subsets. Four attributes will mean 15 subsets, and thus
15 SQL statements. Five attributes will mean 31 statements. How
can I produce the counts without doing an exponential amount of
work as the number of binary attributes increases?
Put another way, given the following create/insert statements... }
create temp table tall ( name char(1) ) with no log ;
insert into tall values ( "A" ) ;
insert into tall values ( "B" ) ;
insert into tall values ( "C" ) ;
insert into tall values ( "K" ) ;
insert into tall values ( "G" ) ;
insert into tall values ( "H" ) ;
insert into tall values ( "I" ) ;
insert into tall values ( "J" ) ;
create temp table nice ( name char(1) ) with no log ;
insert into nice values ( "B" ) ;
insert into nice values ( "C" ) ;
insert into nice values ( "K" ) ;
insert into nice values ( "L" ) ;
insert into nice values ( "M" ) ;
insert into nice values ( "D" ) ;
insert into nice values ( "E" ) ;
insert into nice values ( "F" ) ;
create temp table bold ( name char(1) ) with no log ;
insert into bold values ( "K" ) ;
insert into bold values ( "L" ) ;
insert into bold values ( "M" ) ;
insert into bold values ( "N" ) ;
insert into bold values ( "P" ) ;
insert into bold values ( "Q" ) ;
insert into bold values ( "G" ) ;
insert into bold values ( "H" ) ;
insert into bold values ( "I" ) ;
insert into bold values ( "J" ) ;
{ ...how can I, in SQL, get the following information:
Tall? Nice? Bold? Count
Y Y Y 1
Y Y N 2
Y N Y 4
Y N N 1
N Y Y 2
N Y N 3
N N Y 3
...although not necessarily in this exact format?
- Paul (not a spokesman)
Well, I thought it an interesting problem although many will
doubtless disagree. It did come up in a business context, w/
product IDs rather than letters. }