CODEWITHSUNDEEP
phpmysqlsql
Casper Round
Casper Round

Reputation: 343

Mysql Error on joining three tables

I'm doing a query using (and joining) three tables, but I have an error that i cannot understand on the query I'm doing. I'm using IF, AS and IN in the query statement. This is the query: 

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)

And this is the line causing the error:

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

Upvotes: 0

Views: 56

Answers (1)

Malay M
Malay M

Reputation: 1759

It seems that you wrote two query separately:

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)

Do it together:

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)

Upvotes: 1

Related Questions