MySQL Joins With Case Statements

Question

I am having a bit of trouble with a query. I want to join tables if a case is met. This the query I'm working with. I'm kind of new to these case statements so any help is greatly appreciated!

      SELECT 
        conversation.c_id,
        conversation.user_one,
        conversation.user_two,
        users.name,
        users.lastName
      FROM `conversation` 

      CASE
        WHEN conversation.user_one = 1
        THEN
          INNER JOIN `users`
          ON conversation.two = users.id

        WHEN conversation.user_two = 1
        THEN
          INNER JOIN `users`
          ON conversation.user_one = users.id
      END CASE

      WHERE `user_one` = 1 OR `user_two` = 1

Answer 1

Don't case the entire inner join, do the case on only the 'on' clause in the join. This should work (unless I have typos):

  SELECT 
    conversation.c_id,
    conversation.user_one,
    conversation.user_two,
    users.name,
    users.lastName
  FROM `conversation` 
  INNER JOIN `users`
  on
  users.id =
  CASE
    WHEN conversation.user_one = 1
    THEN conversation.two 
    WHEN conversation.user_two = 1
    THEN conversation.user_one   
 END
 WHERE `user_one` = 1 OR `user_two` = 1

You can also achieve a similiar affect by left joining on each of these conditions and then using the case statement in your select statement to determine which one of the two tables to display records from.