MySQL中使用CASE出错,使用CASE和INNER JOIN的MySQL查询错误

I have a table with user info called 'a' and a table with data from different API's (twitter,foursquare) which in the example, based on the value

of the a.api_type should become b. What I want in the end is to be able to grab the right avatar from the active API (either api_foursquare or api_twitter).

I have been trying to get this to work with this query for a while, but I keep getting this error. Sql is not my strongest point, so any tips on how to fix this would be great :)

"[Err] 1064 - 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

INNER JOIN b ON b.user_id = a.user_id at line 11"

SELECT a.user_id,

a.api_type,

b.avatar,

b.user_id

(CASE

WHEN a.api_type = 0 THEN api_foursquare

WHEN a.api_type = 1 THEN api_twitter

END) as b

FROM a WHERE a.cookie_hash = :cookie_hash

INNER JOIN b ON b.user_id = a.user_id

解决方案

You cannot pick a table to join in a case statement. You should left-join to both tables, and then pick the value from one of them in the case statement, like this:

SELECT a.user_id,

a.api_type,

(case WHEN a.api_type = 0 THEN b.avatar ELSE c.avatar END) as avatar,

(case WHEN a.api_type = 0 THEN b.user_id ELSE c.user_id END) as user_id

FROM a

LEFT OUTER JOIN api_foursquare b ON b.user_id = a.user_id

LEFT OUTER JOIN api_twitter c ON c.user_id = a.user_id

WHERE a.cookie_hash = :cookie_hash

You probably do not need the last expression (the ...as user_id one), because it is going to be equal to a.user_id if there is a row in either api_twitter or api_foursquare that matches a.user_id.

You also have to put the WHERE clause after the FROM clause:

EDIT: Taking into account ypercube's great suggestion, the query would look like this:

SELECT a.user_id,

a.api_type,

COALESCE(b.avatar, c.avatar) as avatar

FROM a

LEFT OUTER JOIN api_foursquare b ON b.user_id = a.user_id AND a.api_type = 0

LEFT OUTER JOIN api_twitter c ON c.user_id = a.user_id and a.api_type = 1

WHERE a.cookie_hash = :cookie_hash