MySQL Conditional Join
As you can see below, I am checking to see if the current user is in user_a
or user_b
columns of table friends.
Depending on where the current user is located, I want to get his corres开发者_StackOverflowponding friend.
Somehow I can't get this syntax to work and wonder if anyone can tell me what's wrong (I get an error on line 3 near IF user_a = 2
.
SELECT *
FROM friends
IF user_a = 2 THEN
JOIN user_profiles ON friends.user_b = user.profiles.user_id
WHERE user_a = 2
AND accepted = 1;
ELSEIF user_b = 2 THEN
JOIN user_profiles ON friends.user_a = user_profiles.user_id
WHERE user_b = 2
AND accepted = 1;
END IF;
You can do it with a UNION:
select f.*, up_a.* from friends f
inner join user_profiles up_a on f.user_a=up_a.user_id
where f.user_b=2 and f.accepted=1
union
select f.*, up_b.* from friends f
inner join user_profiles up_b on f.user_b=up_b.user_id
where f.user_a=2 and f.accepted=1;
You are inventing syntax. The "IF" in MySQL is supported as:
Statement, for use in stored procedures and triggers. Docs: http://dev.mysql.com/doc/refman/5.0/en/if-statement.html
Function, for use in choosing one of alternate expressions for a result column. Docs: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if
You appear to be doing something else entirely.
I believe the syntax you're attempting to use is unsupported. I don't think you can use an IF
conditional to branch your SQL statement in this manner. It may be possible to fit this into a SELECT CASE
.
If you can use local variables, something along these lines may work:
BEGIN
SELECT user_a, user_b INTO local_a, local_b FROM friends;
IF local_a = 2 THEN
SELECT * FROM friends JOIN user_profiles ON friends.user_b = users.profiles.user_id
WHERE user_a = 2 AND accepted = 1;
ELSEIF user_b = 2 THEN
SELECT * FROM friends JOIN user_profiles ON friends.user_b = users.profiles.user_id
WHERE user_b = 2 AND accepted = 1
END IF;
END;
I'm not certain of the validity of this either, but it may send you down the right road. See also: http://dev.mysql.com/doc/refman/5.0/en/select-into-statement.html
精彩评论