CODEWITHSUNDEEP
mysqlcase
Alan A
Alan A

Reputation: 2551

Mysql Case Statement syntax

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.

Upvotes: 0

Views: 1878

Answers (2)

Michael - sqlbot
Michael - sqlbot

Reputation: 179114

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.

Upvotes: 1

Vyktor
Vyktor

Reputation: 20997

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`

Upvotes: 1

Related Questions