title: Conditional selection of columns in MySQL
body: <p>I have a special case where I need to get the username and user image that is the "opposite" of the given user_id. What I mean is, if you have a user id of 10, and you want to get messages that are relevant to that user, then I want the username and user image that is the opposite of the user with user_id of 10. So if someone with user_id of 3 has sent a message to someone with user_id of 10, then I want that message to be selected, but I only want the username and user image of the user that does not have user_id 10, which is the user_id I am given.</p>
  
  <p>I have 3 tables to work with:</p>
  
  <p>The user table:</p>
  
  <pre><code>+--------------+------------------+------+-----+---------+----------------+
  | Field        | Type             | Null | Key | Default | Extra          |
  +--------------+------------------+------+-----+---------+----------------+
  | id           | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
  | email        | varchar(255)     | NO   | MUL | NULL    |                |
  | username     | varchar(40)      | NO   | MUL | NULL    |                |
  | geonameid    | int(11) unsigned | NO   |     | NULL    |                |
  | birthdate    | date             | NO   |     | NULL    |                |
  | hash         | varchar(255)     | NO   |     | NULL    |                |
  | image_id     | int(11) unsigned | YES  |     | NULL    |                |
  | about        | text             | YES  |     | NULL    |                |
  | verified     | int(1)           | NO   |     | 0       |                |
  | notification | int(1)           | NO   |     | 1       |                |
  | joined       | int(11) unsigned | NO   |     | NULL    |                |
  | lastactivity | int(11) unsigned | NO   |     | NULL    |                |
  | ipv4         | int(11) unsigned | YES  |     | NULL    |                |
  | ipv6         | binary(16)       | YES  |     | NULL    |                |
  | deleted      | int(1)           | NO   |     | 0       |                |
  +--------------+------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>The image table:</p>
  
  <pre><code>+-----------+------------------+------+-----+---------+----------------+
  | Field     | Type             | Null | Key | Default | Extra          |
  +-----------+------------------+------+-----+---------+----------------+
  | id        | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
  | name      | varchar(255)     | NO   |     | NULL    |                |
  | width     | int(5) unsigned  | NO   |     | NULL    |                |
  | height    | int(5) unsigned  | NO   |     | NULL    |                |
  | extension | varchar(10)      | NO   |     | NULL    |                |
  | created   | int(11) unsigned | NO   |     | NULL    |                |
  | user_id   | int(11) unsigned | NO   | MUL | NULL    |                |
  | deleted   | int(1)           | NO   |     | 0       |                |
  +-----------+------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>and the messages table:</p>
  
  <pre><code>+--------------+------------------+------+-----+---------+----------------+
  | Field        | Type             | Null | Key | Default | Extra          |
  +--------------+------------------+------+-----+---------+----------------+
  | id           | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
  | message      | text             | NO   |     | NULL    |                |
  | reply_to     | int(11) unsigned | NO   | MUL | 0       |                |
  | from_id      | int(11) unsigned | NO   | MUL | NULL    |                |
  | to_id        | int(11) unsigned | NO   | MUL | NULL    |                |
  | active       | int(1)           | NO   |     | 1       |                |
  | sent         | int(11) unsigned | NO   | MUL | NULL    |                |
  | viewed       | int(1)           | NO   |     | 0       |                |
  | from_deleted | int(1)           | NO   |     | 0       |                |
  | to_deleted   | int(1)           | NO   |     | 0       |                |
  | deleted      | int(1)           | NO   |     | 0       |                |
  +--------------+------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>Right now I have this query:</p>
  
  <pre><code>    SELECT
          message.id,
          message.message,
          message.reply_to AS replyTo,
          message.from_id AS fromId,
          message.to_id AS toId,
          message.sent,
          message.viewed,
          fromuser.username as fromUsername,
          touser.username as toUsername,
          CONCAT('".UPLOAD_URL."/img/', SUBSTR(image.name, 1, 2), '/', SUBSTR(image.name, 3, 2), '/', SUBSTR(image.name, 5, 2), '/', image.name, '-50x50', '.' ,image.extension) AS thumbnail50x50
      FROM
          message AS message
      INNER JOIN user AS fromuser
          ON message.from_id = fromuser.id
      INNER JOIN user AS touser
          ON message.to_id = touser.id
      INNER JOIN image AS image
          ON image.id = fromuser.image_id
      WHERE
          (message.from_id = :user_id OR message.to_id = :user_id) AND
          message.active = 1 AND
          message.deleted = 0
      ORDER BY message.sent DESC;
  </code></pre>
  
  <p>Instead of selecting fromuser.username and selecting touser.username
  and selecting the image of the user that has sent the message,
  I would like to select something like: oppositeuser.username, and the image.name of the opposite user as done in the query above. The special case here is that I need the username and user image of the opposite user of the given user_id, and not both the fromuser and touser.</p>