具有左连接条件的MySQL case语句

I have a MySQL request below and it is not working right.

我在下面有一个MySQL请求,它无法正常工作。

Error:

#1054 - Unknown column 'uid' in 'on clause'

#1054 - 'on子句'中的未知列'uid'

Query:

SELECT 
  u.email,
  CASE 
  WHEN c.from_user_id = '301' THEN c.to_user_id 
  WHEN c.to_user_id = '301' THEN c.from_user_id 
  END AS uid
FROM `contacts` AS c
LEFT JOIN `users` AS u 
ON u.id = uid
HAVING uid >0

How can I write the right request?

我怎样才能写出正确的要求?

1 个解决方案

#1

You can't refer to an column alias in the WHERE clause of the same SELECT, because WHERE is used to find the rows before the SELECT clause operates on them. Put that into a subquery.

您不能在同一SELECT的WHERE子句中引用列别名,因为WHERE用于在SELECT子句对它们进行操作之前查找行。把它放到子查询中。

SELECT u.email, c.uid
FROM (SELECT CASE
                WHEN from_user_id = '301' THEN to_user_id
                ELSE from_user_id
             END AS uid
      FROM contacts
      WHERE from_user_id = '301' OR to_user_id = '301') AS c'
LEFT JOIN users AS u
ON u.id = c.uid