You can't. But assuming you modified your DB as he suggested, then you could write a different query to find the pairs.
Originally Posted by macleodjb
But it will be a messy and complex query.
First of all, I disagree a little bit with Todd's DB design. Let me correct him:
There is no reason at all to have that auto_increment ball_id in that table. The ball_id in no way helps identify a particular record in a useful way.
create table lottery_results (
lottery_id int not null,
ball_number int not null,
ball_type varchar(1) not null,
primary key(lottery_id, ball_number) ) engine = innodb;
So get rid of it and change the primary key to a *composite* key of (lottery_id, ball_number) and you will have a useful key that (a) uniquely identifies each ball and (b) makes looking them up by lottery and ball_number as fast as you want and need.
Just to FIND all the the pairs in the table:
And then to find pairs that match from different lotteries:
CREATE VIEW v_ball_pairs AS
SELECT L1.lottery_id, L1.ball_number AS ball1, L2.ball_number AS ball2
FROM lottery_results AS L1, lottery_results AS L2
WHERE L1.lottery_id = L2.lottery_id
AND L1.ball_number < L2.ball_number
AND L1.ball_type = 'W'
AND L2.ball_type = 'W';
Wasn't that fun? There will be a quiz on Friday.
SELECT P1.lottery_id AS lottery1, P2.lottery_id AS lottery2, P1.ball1, P2.ball2
FROM v_ball_pairs AS P1, v_ball_pairs AS P2
WHERE P1.lottery_id < P2.lottery_id
AND P1.ball1 = P2.ball1
AND P1.ball2 = P2.ball2