Re: SQL puzzle (for entertainment only)
Why not create a table with one record for each letter and extra
fields for the attributes?
Would that make things more simple?
Create the table with fields char_attr1-10 and int_attr1-10 and just
use them as needed. Then create a view that selects the attr fields
but alias's the attributes you are using and does not select the ones
you are not using. Then when you begin to use an attribute just modify
the view. Then the names would make sense and you just give your
manager a query tool and he can select them all out himself.
This will allow much better performance if you were to put millions of
rows in the table also, but would sacrifice a small amount of disk
space. Disk space is cheaper that delveopment resources to write all
those SQLs you have at the bottom.
Just my quick ideas on this.
Jason
On 18 Feb 1998 23:40:54 GMT, proberts@lynx.informix.com (Paul Roberts)
wrote:
>{ 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. }