[replied only to microsoft.public.sqlserver.programming.
Other groups are not official MS groups]
It sounds like you are asking for two different things: one is a
query to retrieve rows representing drawings that have taken place
and one is to pick "all possible outcomes," or the number of
of possible outcomes.
Here's an example of how you might do the first of those. It's not
going to be efficient for large amounts of data, however.
create table Drawings (
DrawNum int identity(1,1),
insert into Drawings values (12,33,23,34,9)
insert into Drawings values (46,21,7,13,22)
insert into Drawings values (21,7,9,13,36)
insert into Drawings values (13,12,11,4,3)
create function RequiredTable(
-- required balls **right-justified in
-- 5-character fields**
) returns table as return
select ltrim(R) as R from (
select substring(r,1,5) as R union all
select substring(r,6,5) union all
select substring(r,11,5) union all
select substring(r,16,5) union all
) T where len(R) > 0
declare required varchar(25)
set required = ' 13 7'
-- format is BALL1BALL2...
select * from Drawings
where DrawNum in (
from Drawings, RequiredTable(required) RT
where R in (Ball1,Ball2,Ball3,Ball4,Ball5)
group by DrawNum
having count(R) = len(required)/5
drop table Drawings
drop function RequiredTable
-- Steve Kass
-- Drew University
-- Ref: D2020AA6-3A79-4DF3-ADE6-593EFA3E43C5
Jim Hubbard wrote: