使用CASE和INNER JOIN的MySQL查询错误 [英] MySQL query error with CASE and INNER JOIN

问题描述

我有一个表，其中包含名为"a"的用户信息，以及一个表，其中包含来自不同API(twitter，foursquare)的数据，该示例中基于值 a.api_type的值应变为b.最后，我想要的是能够从活动的API(api_foursquare或api_twitter)中获取正确的头像.

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).

一段时间以来，我一直试图使它与该查询一起使用，但是我一直收到此错误. Sql不是我的强项，所以任何有关解决此问题的技巧都将很不错:)

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

推荐答案

您不能选择要加入case语句的表.您应该左连接到两个表，然后在case语句中从其中一个表中选择值，如下所示:

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

您可能不需要最后一个表达式(...as user_id一个)，因为如果api_twitter或api_foursquare中存在与a.user_id相匹配的行，则它将等于a.user_id.

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.

您还必须将WHERE子句放在FROM子句之后:

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

编辑:考虑到ypercube的出色建议，查询看起来像这样:

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

这篇关于使用CASE和INNER JOIN的MySQL查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！