MySQL Forums
Forum List  »  Newbie

Re: Joining results from two tables
Posted by: Phillip Ward
Date: January 11, 2018 11:04AM

Whilst an understandable and perfectly "natural" way to arrange your data, I would have to say it is inherently wrong for a database to work with.

Relational databases don't work well with repeated columns.
Querying them is a mess - for example, in which weeks did "19" get drawn?
select * 
from table1 
where 19 in ( ball1, ball2, ball3, ball4, ball5 )

Ick!

Relational databases work well with rows; even if there are lots (and lots) of them. So, for a single week, you might have something like:

select * 
from draws ; 

+------+------------+ 
| id   | draw_date  | 
+------+------------+ 
| 1048 | 06/10/2017 | 
+------+------------+ 

select * from draw_balls ;

+---------+-----+------+
| draw_id | seq | ball | 
+---------+-----+------+
|    1048 |   0 |    1 | 
|    1048 |   1 |    9 | 
|    1048 |   2 |   15 | 
|    1048 |   3 |   19 | 
|    1048 |   4 |   25 | 
|    1048 |   5 |    7 | 
+---------+-----+------+

Now you can easily search for any draw that contains a particular number ...
select draw_id 
from draw_balls
where ball = 19 
group by draw_id 
order by draw_id ; 

+---------+
| draw_id | 
+---------+
|    1051 |
|    1048 |
|    1046 |
+---------+

... or, to find draws with any combination of numbers:

select draw_id 
from draw_balls
where ball in ( 19, 32, 48 )
group by draw_id 
having count( * ) = 3 
order by draw_id ; 

+---------+
| draw_id | 
+---------+
|    1046 |
+---------+

Regards, Phill W.

Options: ReplyQuote


Subject
Written By
Posted
Re: Joining results from two tables
January 11, 2018 11:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.