newbie needs a pointer

Hi all,

Although I am new to MySQL its query capabilities intrigue me. At this
moment I am stuck though and I am hoping that someone can give me a
pointer on how to solve my problem.

Basically I am trying to figure out what prize is attached to a lottery
ticket (if any).

First of all I tried to do some normalization (bear with me if it is not
perfect). I came up with the following tables:

The ticket entity with the associated draw id:

+-----------+---------+
| ticket_id | draw_id |
+-----------+---------+
| 1 | 1206 |
| 2 | 1207 |
+-----------+---------+

The draw entity with the draw date, ball id of the bonus ball and the
associated prizes:

+---------+------------+---------+---------+--------------------+
| draw_id | date | ball_id | match_6 | match_5_plus_bonus |
+---------+------------+---------+---------+--------------------+
| 1207 | 2007-07-18 | 23 | 2838118 | 174653 |
| 1206 | 2007-07-14 | 11 | 5248063 | 201848 |
| 1205 | 2007-07-07 | 22 | 479142 | 73714 |
+---------+------------+---------+---------+--------------------+

continued below...

+---------+---------+---------+
| match_5 | match_4 | match_3 |
+---------+---------+---------+
| 2352 | 95 | 10 |
| 3003 | 101 | 10 |
| 1308 | 57 | 10 |
+---------+---------+---------+

The ball entity (maybe a bit ott but I think I need this table):

+---------+--------+
| ball_id | number |
+---------+--------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 10 |
| 11 | 11 |
| 12 | 12 |
| 13 | 13 |
| 14 | 14 |
| 15 | 15 |
| 16 | 16 |
| 17 | 17 |
| 18 | 18 |
| 19 | 19 |
| 20 | 20 |
| 21 | 21 |
| 22 | 22 |
| 23 | 23 |
| 24 | 24 |
| 25 | 25 |
| 26 | 26 |
| 27 | 27 |
| 28 | 28 |
| 29 | 29 |
| 30 | 30 |
| 31 | 31 |
| 32 | 32 |
| 33 | 33 |
| 34 | 34 |
| 35 | 35 |
| 36 | 36 |
| 37 | 37 |
| 38 | 38 |
| 39 | 39 |
| 40 | 40 |
| 41 | 41 |
| 42 | 42 |
| 43 | 43 |
| 44 | 44 |
| 45 | 45 |
| 46 | 46 |
| 47 | 47 |
| 48 | 48 |
| 49 | 49 |
+---------+--------+

The following tables are needed because of the many to many relationships:

The balls associated with a ticket:

+----------------+-----------+---------+
| ticket_ball_id | ticket_id | ball_id |
+----------------+-----------+---------+
| 1 | 1 | 3 |
| 2 | 1 | 13 |
| 3 | 1 | 22 |
| 4 | 1 | 32 |
| 5 | 1 | 44 |
| 6 | 1 | 46 |
+----------------+-----------+---------+

The balls associated with a draw:

+--------------+---------+---------+
| draw_ball_id | draw_id | ball_id |
+--------------+---------+---------+
| 1 | 1207 | 12 |
| 2 | 1207 | 31 |
| 3 | 1207 | 33 |
| 4 | 1207 | 39 |
| 5 | 1207 | 41 |
| 6 | 1207 | 45 |
| 7 | 1206 | 15 |
| 8 | 1206 | 27 |
| 9 | 1206 | 35 |
| 10 | 1206 | 37 |
| 11 | 1206 | 38 |
| 12 | 1206 | 39 |
| 13 | 1205 | 3 |
| 14 | 1205 | 9 |
| 15 | 1205 | 10 |
| 16 | 1205 | 12 |
| 17 | 1205 | 46 |
| 18 | 1205 | 47 |
+--------------+---------+---------+

My first step was to figure out how many balls matched for a given
draw using the following query:

mysql> SELECT COUNT(ticket_ball.ball_id) AS matches
-> FROM `ticket_ball`, `draw_ball`
-> WHERE `ticket_id`=1 and `draw_id`=1205
-> AND `ticket_ball`.`ball_id`=`draw_ball`.`ball_id`
-> ;

This results in:

+---------+
| matches |
+---------+
| 2 |
+---------+

As far as know I can also use a inner join to get this result although
this method is still unclear to me.

mysql> SELECT COUNT(ticket_ball.ball_id)
-> FROM ticket_ball
-> INNER JOIN draw_ball
-> ON(ticket_ball.ball_id=draw_ball.ball_id)
-> WHERE draw_id=1205
-> ;

+----------------------------+
| COUNT(ticket_ball.ball_id) |
+----------------------------+
| 2 |
+----------------------------+

Although this is a start I cannot figure out how to elaborate this query
so that it returns the prize won taking into account the bonus ball as
well.

So, for instance for draw 1205, if 5 balls match and the bonus ball
matches it should return 73714. If 5 balls match it should return 1308.

The CASE expression might come into play but I fail to see how to tie this
in.

As stated before, some pointers would be highly appreciated.

> The draw entity with the draw date, ball id of the bonus ball and the
> associated prizes:
>
> +---------+------------+---------+---------+--------------------+
> | draw_id | date | ball_id | match_6 | match_5_plus_bonus |
> +---------+------------+---------+---------+--------------------+
> | 1207 | 2007-07-18 | 23 | 2838118 | 174653 |
> | 1206 | 2007-07-14 | 11 | 5248063 | 201848 |
> | 1205 | 2007-07-07 | 22 | 479142 | 73714 |
> +---------+------------+---------+---------+--------------------+

in my humble opinion there is too much stuff in one table. let try to split
that to atomic parts.
"The ball entity" table - what's that? looks a bit redundant.

there is a too late for me so I dont give you a solution but I think
re-arrange data model will be helpful in your situation.

regards,
R.

On Tue, 24 Jul 2007 00:43:40 +0200, aaooo54 wrote:

>> The draw entity with the draw date, ball id of the bonus ball and the
>> associated prizes:
>>
>> +---------+------------+---------+---------+--------------------+
>> | draw_id | date | ball_id | match_6 | match_5_plus_bonus |
>> +---------+------------+---------+---------+--------------------+
>> | 1207 | 2007-07-18 | 23 | 2838118 | 174653 |
>> | 1206 | 2007-07-14 | 11 | 5248063 | 201848 |
>> | 1205 | 2007-07-07 | 22 | 479142 | 73714 |
>> +---------+------------+---------+---------+--------------------+
>
> in my humble opinion there is too much stuff in one table. let try to split
> that to atomic parts.
> "The ball entity" table - what's that? looks a bit redundant.
>
> there is a too late for me so I dont give you a solution but I think
> re-arrange data model will be helpful in your situation.
>
> regards,
> R.

Thanks for your reply, I agree that the "ball entity table" seems
redundant, I will try and remove that. The reason I added it is because I
was trying to identify all objects that might come into play.

With regards to the draw_id table I cannot figure out how to make that
more atomic than it already is unless I misunderstand the meaning of
"atomic". I used the following definition for "atomic":

By atomic we mean that there are no sets of values within a column.

Which I took from:

http://dev.mysql.com/tech-resources/...alization.html

There are no sets of values in the columns of that table, the draw prizes
(match_6, match_5_plus_bonus, etc.) may seem odd but are basically just
ordinary fields. Every draw has those fields, no more, no less.

Maybe you can help me out where my mistake with regard to the atomic
part lies.

Thanks again.