Can I put a condition on a JOIN clause in MySQL?

For example suppose I have the following join

join on p.match_id = o.match_id

Could I specify instead that if p.match_id is even then

join on p.match_id = o.match_id - 1

But开发者_C百科 if it is odd then

join on p.match_id = o.match_id + 1

I have sequential match_id such that consecutive values represent a single match. Therefore to work out an opponent for a given match_id I need the above

Edited so it doesn't use CASE. The subquery returns a table with one field: matchid (equals match_id+1 when match_id is even and match_id-1 when match_id is odd).

SELECT matchid
  FROM ( SELECT match_id + 1 - 2*(match_id % 2)
                AS matchid
           FROM p
       ) AS p2
  JOIN o
    ON p2.matchid = o.match_id
;

Since you mention that you are using one table and 2 sequentials ids for one pair of opponents, you can also use the following as a subquery or a view:

SELECT match_id
     , ((match_id + 1) DIV 2) AS pair
  FROM p
;

It will give you a table that you can then collerate to itself:

| match_id | pair |
|     1    |   1  |
|     2    |   1  |
|     3    |   2  |
|     4    |   2  |
|     5    |   3  |
|     6    |   3  |

If you want to join one table with another where the 2nd table's ID is even only, try this:

JOIN ON p.match_id = o.match_id AND o.match_id % 2 = 0

or where 2nd table's ID is odd:

JOIN ON p.match_id = o.match_id AND o.match_id % 2 = 1

I think if you wish to join on even p.match_id values only, you'll need to do that a bit differently:

join on p.match_id = o.match_id and p.match_id = p.match_id div 2 * 2

Similarly, if they should be the odd ones:

join on p.match_id = o.match_id and p.match_id = p.match_id div 2 * 2 + 1

But like others, I'm curious about why you would need that. An id column is only that, an id column. Normally you shouldn't be interested in the particular characteristics of the id column's value.