Mysql Case Statement syntax

Question

Can anyone tell me where the syntax aerror here is:

SELECT `t`.*, `u`.`first_name`, `u`.`second_name` 
FROM `tickets` `t`

(CASE WHEN `t`.`user_id` IS NOT NULL
THEN 
JOIN `adminbb` `u` ON `t`.`admin_id`=`u`.`admin_id`
ELSE 
JOIN `users` `u` ON `t`.`user_id`=`u`.`user_id`
END) 

WHERE `t`.`ticket_id` =1
ORDER BY  `t`.`ticketText_id` 
LIMIT 0,3

Error says: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(CASE WHEN t.user_id IS NOT NULL THEN JOIN adminbb u ON t.`admin_id' at line 3

Thanks Alan.

http://stackoverflow.com/questions/2761574/mysql-use-case-else-value-as-join-parameter. It might help you — PSR, Sep 18 '13 at 11:03

score 1 · Accepted Answer · answered Sep 18 '13 at 11:08

1

MySQL doesn't support conditional join table selection. ^[1][2]

SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references ...

And table reference is defined without complex expressions like CASE.

You can use:

SELECT IF(t.user_id IS NULL, a.user_id, u.user_id) AS user_id, ...
FROM ...
JOIN `adminbb` `a` ON `t`.`admin_id`=`u`.`admin_id`
JOIN `users` `u` ON `t`.`user_id`=`u`.`user_id`

answered Sep 18 '13 at 11:08

Vyktor

20,559
6
64
96

Thanks, your solution doesn't quite fit because you are making two joins, I want to make one join depending on the value of t.user_id being NULL or NOT NULL – Alan A Sep 18 '13 at 11:55
@AlanA not possible in mysql, sorry. – Vyktor Sep 18 '13 at 11:55
I don't understand, what is not possible, what I'm trying to achieve or two joins in your example? – Alan A Sep 18 '13 at 11:57
It's not possible to choose which table to join based on on condition. – Vyktor Sep 18 '13 at 12:04
I think the only solution is to use a language such as PHP to achieve my goal, I would select all, parse each result and perform a seperate join for each row depending on the user_id NULL or NOT NULL – Alan A Sep 18 '13 at 12:31

score 1 · Answer 2 · answered Sep 18 '13 at 14:27

This is not difficult to accomplish in MySQL, once you conceptualize the what you're asking the server to do.

I have reversed the IS NOT NULL test against t.user_id because your original case statement seemed to be backwards, joining admin when t.user_id isn't null and joining users when t.user_id is null, which seems impossible. Aside from that, here's a query that fetches the first_name and second_name by joining user when possible and joining admin otherwise. The joins are LEFT joins because we need to return the rows from 'ticket' regardless of which table is joinable.

SELECT t.*, 
       COALESCE(u.first_name,a.first_name) as first_name, 
       COALESCE(u.second_name,a.second_name) as second_name
  FROM `tickets` `t`
  LEFT JOIN `users` `u` ON `t`.`user_id`=`u`.`user_id`
  LEFT JOIN `adminbb` `a` ON `t`.`admin_id`=`a`.`admin_id` AND t.user_id IS NULL
 WHERE `t`.`ticket_id` =1
 ORDER BY  `t`.`ticketText_id` 
 LIMIT 0,3;

The COALESCE() function returns the leftmost non-null value from among its arguments.

The LEFT JOIN users will pick up the values from "u" if they exist and if t.user_id isn't null, and the LEFT JOIN adminbb will look for the values from "a" only if t.user_id is null.

Conditions in the ON clause only have to be testable expressions, they don't actually have to be "about" the table you are joining. In the case of the join against adminbb, the new join condition is actually about the table on the left rather than the table on the right, but this is still quite legitimate and will still prevent the join from being attempted when it's not needed.

Mysql Case Statement syntax

2 Answers2