[SOLVED] Query Condition is Making My Head Hurt

[josborne]

I have been struggling with a particular query for some time and I just do not seem to be able to wrap my brain around the solution.

 

What I am trying to accomplish is pretty simple in concept: I have a table of race results and I need to be able to compare the results of one rider (Rider_ID=1) to those of another (Rider_ID=2) only when the first rider has position equal to 1.

 

For example, given this set of data:

 

Race_ID...Rider_ID............Position.............Race_Time
7.............1....................1....................00:43:02
7.............2....................2....................00:43:05
7.............4....................3....................00:43:11
7............10....................4....................00:43:11
7.............6....................5....................00:43:20
8.............2....................1....................00:45:53
8.............4....................2....................00:45:54
8.............8....................3....................00:45:56
8.............9....................4....................00:45:57
8.............1....................5....................00:45:58
9.............1....................1....................00:44:12
9............10....................2....................00:44:15
9.............2....................3....................00:44:19
9.............4....................4....................00:44:26
9.............6....................5....................00:44:30
10...........2....................1....................00:44:45
10...........8....................2....................00:44:46
10...........4....................3....................00:44:46
10...........6....................4....................00:44:51
10...........7....................5....................00:44:55

 

I would expect the following results:

Race_ID...Rider_ID............Position.............Race_Time
7.............1....................1....................00:43:02 
7.............2....................2....................00:43:05
9.............1....................1....................00:44:12
9.............2....................3....................00:44:19

 

 

I hope that make sense.

 

I have tried so many things that have not worked. I am relatively certain a self join is needed but I can't figure out how to structure it.

 

 

 

CREATE TABLE `Results_tbl` (
`Race_ID` int(10) NOT NULL,
`Rider_ID` int(5) NOT NULL,
`Position` int(2) default NULL,
`Race_Time` time default NULL,
PRIMARY KEY  (`Result_ID`),
UNIQUE KEY `Race_ID_2` (`Race_ID`,`Rider_ID`),
KEY `Race_ID` (`Race_ID`),
KEY `Season` (`Season`),
KEY `Rider_ID` (`Rider_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=7541 DEFAULT CHARSET=latin1

 

 

 

INSERT INTO `Results_tbl` (`Race_ID`, `Rider_ID`, `Position`, `Race_Time`) VALUES
(37, 1, 1, '00:43:02'),
(37, 2, 2, '00:43:05'),
(37, 4, 3, '00:43:11'),
(37, 10, 4, '00:43:11'),
(37, 6, 5, '00:43:20'),
(38, 2, 1, '00:45:53'),
(38, 4, 2, '00:45:54'),
(38, 8, 3, '00:45:56'),
(38, 9, 4, '00:45:57'),
(38, 1, 5, '00:45:58'),
(40, 1, 1, '00:44:12'),
(40, 2, 2, '00:44:15'),
(40, 10, 3, '00:44:19'),
(40, 4, 4, '00:44:26'),
(40, 6, 5, '00:44:30'),
(10, 2, 1, '00:44:45'),
(10, 8, 2, '00:44:46'),
(10, 4, 3, '00:44:46'),
(10, 6, 4, '00:44:51'),
(10, 7, 5, '00:44:55');

 

[onedumbcoder]

SELECT 
      r1.id AS rpo , r2.*, 
      (UNIX_TIMESTAMP(r2.Race_Time) - UNIX_TIMESTAMP(r1.Race_Time)) AS diff 
FROM 
      Results_tbl AS r1 LEFT JOIN Results_tbl AS r2 ON(r2.id!=r1.id)
WHERE
      r1.position = 1
ORDER BY r1.id

[josborne]

I tried that with both rider_ID and race_ID in place of the r1.id and both gave me the results for every rider and about 2 million rows.

 

All I am looking for is all rows where rider_ID 1 also has Position 1 and rider_ID 2 exists within the same Race_ID. So, for each row where that is true, two rows should result.

 

I do appreciate your help though.

[roopurt18]

If your version of MySQL has good support for sub-queries, the following may work:

SELECT
    b.*
FROM (
    SELECT `Race_ID` FROM `Results_tbl`
    WHERE `Rider_Id`=1 AND `Position`=1
) AS a
INNER JOIN `Results_tbl` AS b ON a.Race_ID=b.Race_ID
WHERE b.`Rider_ID` IN ( 1, 2 )
ORDER BY b.`Race_ID`, b.`Position`

[josborne]

Bingo!

 

Thanks. I even understand the query. I have never seen an IN clause before and that will be useful.

[roopurt18]

IN ( ... ) works great when you have a PHP array of possible values because of the implode() function (in PHP).

 

array_fill() (also a PHP function) comes in handy if you use prepared queries with the ? placeholders.

[josborne]

Man, I hope I am not wearing out my my welcome 

 

After playing with this for a while, I realized that what I really need is to list every Race_ID where Rider_ID=1 is greater than Rider_ID=2 rather than just when Rider_ID=1 AND Position=1

 

Any suggestions?

[roopurt18]

Do you mean to say that RiderID1_Position must also be greater than RiderId2_Position?

[josborne]

What I mean is that I need to know every time the Position column is lower for Rider_ID 1 than it is for Rider_ID 2 where the Race_ID is the same.

 

I know that is confusing. here is an example of the output:

 

Race_ID...Rider_ID............Position.............Race_Time
7.............1....................2....................00:43:02
7.............2....................4....................00:43:05
7.............4....................3....................00:43:11
7............10....................4....................00:43:11
7.............6....................5....................00:43:20
8.............2....................1....................00:45:53
8.............4....................2....................00:45:54
8.............8....................3....................00:45:56
8.............9....................4....................00:45:57
8.............1....................5....................00:45:58
9.............1....................1....................00:44:12
9............10....................2....................00:44:15
9.............2....................3....................00:44:19
9.............4....................4....................00:44:26
9.............6....................5....................00:44:30
10...........2....................8....................00:44:45
10...........1....................2....................00:44:46
10...........4....................3....................00:44:46
10...........6....................4....................00:44:51
10...........7....................5....................00:44:55

 

Would return this result:

 

Race_ID...Rider_ID............Position.............Race_Time
7.............1....................2....................00:43:02
7.............2....................4....................00:43:05
9.............1....................1....................00:44:12
9.............2....................3....................00:44:19
10...........2....................8....................00:44:45
10...........1....................2....................00:44:46

 

 

 

 

 

 

[fenway]

I don't follow.

[josborne]

I just figured it out a little while ago. Sorry for making it confusing. I was trying to provide as much detail as I could.

 

Anyway, this is what I was trying to do:

 

SELECT COUNT(a.Race_ID)
FROM (SELECT Rider_ID, Position, Race_ID
FROM Results_tbl
WHERE Rider_ID=1) a,
(SELECT Rider_ID, Position, Race_ID
FROM Results_tbl
WHERE Rider_ID=2) b
WHERE a.Race_ID=b.Race_ID
AND a.Position<b.Position
AND a.Position!=0