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

No, I don't think you can use `CASE` statement there like the way you are trying. You may want to look at dynamic query. — Rahul, Aug 22 '14 at 19:01
I'd avoid dynamic here...he can do as a pair of left joins iand cases in the select statement instead as well, but I don't think dynamic will mneet his needs. — Twelfth, Aug 22 '14 at 19:03
Yeah I see what you where saying now from Twelfth's post. It's working now. Thanks for the help! — Brian Moreno, Aug 22 '14 at 19:08

score 9 · Accepted Answer · answered Aug 22 '14 at 19:01

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.

What about different tables? – TheRealChx101 Oct 20 '22 at 10:20 — TheRealChx101, Oct 20 '22 at 10:20

MySQL Joins With Case Statements

1 Answers1

Linked