如何在MySQL联接查询中使用IF语句? [英] How do I use an IF statement in an MySQL join query?
本文介绍了如何在MySQL联接查询中使用IF语句?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个SQL查询,该查询根据条件以不同的方式连接表.
I have a SQL query that left joins a table in different ways depending on a condition.
SELECT m.id, u.first_name AS otherUser
FROM matches AS m
IF (u.id=m.user2ID)
LEFT JOIN users AS u ON u.id = m.user1ID
ELSE
LEFT JOIN users AS u ON u.id = m.user2ID
ENDIF
WHERE m.user1ID=2 OR m.user2ID=2
matches
是具有整数列user1ID和user2ID的表. users
是一个包含我的Web应用程序用户的表. users
有一个名为first_name
的VARCHAR字段.
matches
is a table with integer columns user1ID and user2ID. users
is a table containing users of my web application. users
has a VARCHAR field called first_name
.
此查询的目的是获取与当前用户匹配的用户名.
The intention of this query is to get the names of the users matched with the current user.
但是,MySQL返回此错误:
However, MySQL returns this error:
#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 'IF (u.id=m.user2ID) LEFT JOIN users
AS u ON u.id = m.user1ID ELSE LEFT JOIN user' at line 3
为什么?
推荐答案
将条件指定为ON子句的一部分:
Specify the condition as part of the ON clause:
SELECT m.id, u.first_name AS otherUser
FROM matches AS m
LEFT JOIN users AS u ON (u.id=m.user2ID and u.id = m.user1ID) or (u.id<>m.user2ID and u.id = m.user2ID)
WHERE m.user1ID=2 OR m.user2ID=2
做同样事情的另一种方法:
Another way to do the same thing:
SELECT m.id, u.first_name AS otherUser
FROM matches AS m
LEFT JOIN users AS u ON IF(u.id=m.user2ID,u.id = m.user1ID,u.id = m.user2ID)
WHERE m.user1ID=2 OR m.user2ID=2
这篇关于如何在MySQL联接查询中使用IF语句?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文