Using CASE, WHEN, THEN, END in a select query with MySQL

Question

I'm working on a baseball related website. I have a table with a batting lineup for two baseball teams:

+----+----------+--------------+--------+
| id | playerId | battingOrder | active |
+----+----------+--------------+--------+

Batting order is an integer between 1 and 20. This corresponds to the following logic:

1. Batting Order 1-9 — Away Team Lineup
2. Batting Order 10 — Away Team Pitcher
3. Batting Order 11-19 — Home Team Lineup
4. Batting Order 20 — Home Team Pitcher

The active field is a tinyint 0 or 1, representing the pitcher on the mound and the batter on the plate.

Known Fact: There will always be one active pitcher from one team and one active batter from the opposite team.

I need to write a query that returns a row for a home team player that corresponds to the next batter in the battingOrder. (the one that that occurs after the active batter's battingOrder)

Example:

1. If the player in battingOrder 13 is active, the query should return the player in batting order 14.
2. If the player in battingOrder 19 is active, the query should return the player in batting order 11 (the lineup loops back to the first player for the team).

I've never used a CASE query before, but I came up with the following:

SELECT *
  FROM lineups
 WHERE battingOrder = 
       CASE (
           SELECT battingOrder
             FROM lineups
            WHERE battingOrder > 10 AND active = 1
            LIMIT 1
       )
       WHEN 11 THEN 12
       WHEN 12 THEN 13
       WHEN 13 THEN 14
       WHEN 14 THEN 15
       WHEN 15 THEN 16
       WHEN 16 THEN 17
       WHEN 17 THEN 18
       WHEN 18 THEN 19
       WHEN 19 THEN 11
       END
 LIMIT 1;

It seems to work, but what edge cases and/or pitfalls have I walked into? Is this efficient? I'm particulary interested in a solution to my problem that does not use a nested query.

Solution

Select LNext.player As NextPlayer
From lineups As L
    Left Join lineups As LNext
        On LNext.BattingOrder Between 11 And 20
            And LNext.BattingOrder  = Case
                                        When L.BattingOrder  = 19 Then 11
                                        Else L.BattingOrder  + 1
                                        End
Where L.battingOrder Between 11 And 20
    And L.active = 1

In fact, you could make it handle both home and away like so:

Select LNext.player As NextPlayer
From lineups As L
    Left Join lineups As LNext
        On LNext.BattingOrder  = Case
                                    When L.BattingOrder  = 19 Then 11
                                    When L.BattingOrder  = 9 Then 1
                                    Else L.BattingOrder  + 1
                                    End
Where L.active = 1

Comments

[Stephen]

This doesn't seem right. Why are you looking at the id column? It should be the battingOrder column.

[Thomas]

@Stephen - Yes. That's a typo. Fixed.

[Stephen]

Well, I've used your query verbatim with no result. I have a player in batting order position 19 as active, but both of your queries return no result.

[Thomas]

@Stephen - Do you mean you have a player at BO position 18 and 19 that are both active?

[Stephen]

Okay, I currently have a player in the database, whose battingOrder is 19, and Active is 1. My query returns the player at position 11 (correct) and your query returns a null result set.

[Thomas]

@Stephen - Given your update, I now see that "active" doesn't mean "playing" it means "currently batting". Just remove the Active = 1 criteria from the Left Join.

[Thomas]

@Stephen - Keep in mind that the second query will return the "active" batter for both teams.

[Thomas]

@Stephen - Also I noted that you jump at 19 to 11 so I've updated my case statement appropriately.

[Stephen]

Bingo. Just what I needed. Thanks!