MySQL Query: get all matches from the matches table along with the name of the team from the teams table given that when …

I have the following table structures:

matches:

+-------------------+---------------------------+------+-----+-------------------+-----------------------------+
| Field             | Type                      | Null | Key | Default           | Extra                       |
+-------------------+---------------------------+------+-----+-------------------+-----------------------------+
| id                | bigint(20)                | NO   | PRI | NULL              | auto_increment              |
| creator_id        | bigint(20)                | NO   |     | NULL              |                             |
| mode              | enum('versus','freeplay') | NO   |     | NULL              |                             |
| name              | varchar(100)              | NO   |     | NULL              |                             |
| team_1_id         | varchar(100)              | NO   |     | NULL              |                             |
| team_2_id         | varchar(100)              | NO   |     | NULL              |                             |
+-------------------+---------------------------+------+-----+-------------------+-----------------------------+

teams:

+开发者_Python百科--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| creator_id   | bigint(20)   | NO   | MUL | NULL    |                |
| name         | varchar(100) | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

I need a query where we get all matches from the matches table along with the name of the team from the teams table given that when mode is "versus" the name of the team is taken from the teams table but when the mode is "freeplay" the name of the team is team_1_id or team_2_id themselves (they can hold strings, that is why they are varchar instead of int) without going to the teams table.

Use:

SELECT m.id,
       m.creator_id,
       m.mode,
       m.name,
       m.team_1_id,
       m.team_2_id
  FROM MATCHES m
 WHERE m.mode = 'freeplay'
UNION ALL
   SELECT m.id,
          m.creator_id,
          m.mode,
          m.name,
          t1.name,
          t2.name
     FROM MATCHES m
LEFT JOIN TEAMS t1 ON t1.id = m.team_1_id
LEFT JOIN TEAMS t2 ON t2.id = m.team_2_id
    WHERE m.mode = 'versus'

SELECT CASE mode WHEN 'versus' THEN t1.name ELSE team_1_id END AS name FROM matches LEFT JOIN teams t1 ON t1.id=team_1_id

Do the same for team 2 and add where clause to suit your need

First, I would suggest you change your table structure slightly. Instead of using team_X_id for a name or an id, use it only for an id. Add an additional column for team_X_name that you can put the string in. That way you can define foreign keys and have the correct datatype. Set the team_X_id field to null and team_X_name to the team name when in freeplay mode, and set the team_X_id to the team id in versus mode.

That said, this should do what you want:

SELECT mode,
       IF(team_1.id IS NULL, team_1_id, team_1.name),
       IF(team_2.id IS NULL, team_2_id, team_2.name),
  FROM matches
       LEFT JOIN teams AS team_1 ON (matches.team_id_1=team_1.id)
       LEFT JOIN teams AS team_2 ON (matches.team_id_2=team_2.id);

edit: Actually, perhaps I misunderstood the design. If you are saying mode 'freeplay' flag means neither team_X_id will be an actual team id then you need a slightly different query:

SELECT mode,
       IF(mode = 'freeplay' OR team_1.id IS NULL, team_1_id, team_1.name),
       IF(mode = 'freeplay' OR team_2.id IS NULL, team_2_id, team_2.name),
  FROM matches
       LEFT JOIN teams AS team_1 ON (matches.team_id_1=team_1.id)
       LEFT JOIN teams AS team_2 ON (matches.team_id_2=team_2.id);

But I would strongly suggest improving your DB design.