Lottery results query

[potzo]

Hi,
I am trying to come up with a query that would count the number wins/occurrences (for 6, 5, 4, 3, 2, and 1 results )that you would have had using a given group of numbers for a period of several years using a table with the lottery numbers that came up within the last few years;
the table is like this:
Date no1 no2 no3 no4 no5 no6

1993-08-15 06 07 30 17 02 42
1993-08-22 44 20 17 03 38 45
1993-08-29 42 30 36 05 04 06
1993-09-05 05 14 07 49 35 19
1993-09-12 42 28 41 15 22 38
1993-09-19 18 04 16 35 02 17
and so forth....

I am not familiarized with complex queries so obviously I came up with a few queries that I'm sure it can be written in less or just one query;
My thought was to have a query that would check for six numbers:
SELECT * FROM table WHERE
(no1 = ".$in1." OR no1 = ".$in2." OR no1 = ".$in3." OR no1 = ".$in4." OR no1 = ".$in5." OR no1 = ".$in6.")
AND
(no2 = ".$in1." OR no2 = ".$in2." OR no2 = ".$in3." OR no2 = ".$in4." OR no2 = ".$in5." OR no2 = ".$in6.")
AND
(no3 = ".$in1." OR no3 = ".$in2." OR no3 = ".$in3." OR no3 = ".$in4." OR no3 = ".$in5." OR no3 = ".$in6.")
AND
(no4 = ".$in1." OR no4 = ".$in2." OR no4 = ".$in3." OR no4 = ".$in4." OR no4 = ".$in5." OR no4 = ".$in6.")
AND
(no5 = ".$in1." OR no5 = ".$in2." OR no5 = ".$in3." OR no5 = ".$in4." OR no5 = ".$in5." OR no5 = ".$in6.")
AND
(no6 = ".$in1." OR no6 = ".$in2." OR no6 = ".$in3." OR no6 = ".$in4." OR no6 = ".$in5." OR no6 = ".$in6.")
Now you can imagine what kind of queries I could come up with for finding out the numbers of occurences for 1, 2, 3, 4 or 5 results....

Can anybody give me some suggestions please....

 

[johnwm]

If the order in which the numbers are drawn is not significant then normalise your data so that your table has a field for date and a field for drawn number. Have a look at

http://r937.com/relational.html

for a better understanding of database design

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints