php - Mysql 连接三个表时出错

Question

我正在使用(并连接)三个表进行查询，但在我正在执行的查询中出现一个我无法理解的错误。我在查询语句中使用 IF、AS 和 IN。这是查询:

SELECT DISTINCT users.id, users.firstname, users.lastname, friends.recipient, friends.sender, message.sender, message.recipient, message.date, message.time
FROM users 
JOIN friends
    ON users.id IN (friends.sender, friends.recipient)
JOIN message 
    ON (users.id = message.recipient OR users.id = message.sender)
WHERE 75 IN (friends.sender,friends.recipient) 
    OR friends.status = 1 IF(message.recipient = 75, 'message.recieved', 'message.sent') AS message.direction 
 FROM message WHERE message.recipient IN (75, 129) AND sender IN (75, 129)

这是导致错误的行:

IF(message.recipient = 75, 'message.recieved', 'message.sent') AS message.direction

Answer 1

看来您分别编写了两个查询:

SELECT DISTINCT users.id, users.firstname, users.lastname,
friends.recipient, friends.sender, message.sender,
message.recipient, message.date, message.time
FROM users 
JOIN friends
ON users.id IN (friends.sender, friends.recipient)
JOIN message 
ON (users.id = message.recipient OR users.id = message.sender)
WHERE 75 IN (friends.sender,friends.recipient) 
OR friends.status = 1

<小时/>

SELECT  IF(message.recipient = 75, 'message.recieved',
'message.sent') AS message.direction FROM message WHERE
message.recipient IN (75, 129) AND sender IN (75, 129)

一起做:

SELECT DISTINCT users.id, users.firstname, users.lastname, friends.recipient, friends.sender, message.sender, message.recipient, message.date, message.time ,  IF(message.recipient = 75, 'message.recieved', 'message.sent') AS message.direction
FROM users 
JOIN friends
    ON users.id IN (friends.sender, friends.recipient)
JOIN message 
    ON (users.id = message.recipient OR users.id = message.sender)
WHERE ( 75 IN (friends.sender,friends.recipient) 
    OR friends.status = 1 )
    AND message.recipient IN (75, 129) 
    AND message.sender IN (75, 129)