Miles Miles - 3 months ago 7
MySQL Question

SQL JOIN with conditions

I have a

conversation
table which contains two users ids as foreign keys, and the user table which contains the users details. I want to write a query which returns the
conversation
table joined to the user table but displaying the name and surname of the user whose id wasn't sent as the parameter.

CREATE TABLE `conversation` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_one_id` int(11) NOT NULL,
`user_two_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
)

CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`surname` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
)


For example I have

Conversation:

id user_one_id user_two_id
1 1 2


User:

id name surname
1 userone_name userone_surname
2 usertwo_name usertwo_surname


I want a query that will return user_two's name and surname in the join, not user one.
My current query:

SELECT c.id, c.user_one_id, c.user_two_id, u.name, u.surname * FROM conversation c
JOIN user u
WHERE c.user_one_id = 1
OR c.user_two_id = 1
AND IF (c.user_one_id = u.id, c.user_two_id = u.id, c.user_one_id = u.id)
GROUP BY c.id
ORDER BY c.date DESC;

Answer

[INNER] JOIN should have an ON clause. (I consider it a flaw that MySQL allows you to omit it.)

The join criteria would have to be: Give me the user of the conversation that is not user 1.

SELECT c.id, c.user_one_id, c.user_two_id, u.name, u.surname
FROM conversation c
JOIN user u ON u.id IN (c.user_one_id, c.user_two_id) AND u.id <> 1
WHERE c.user_one_id = 1 OR c.user_two_id = 1
ORDER BY c.date DESC;
Comments