Geek Answers Handbook

MySQL conditional join

As you can see below, I check if the current user is in columns user_aor friendsuser_b tables .

Depending on where the current user is located, I want to get his corresponding friend.

Somehow I can't get this syntax to work and wonder if anyone can tell me what happened (I get an error message on line 3 next to 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;
+5
source share
3 answers

You can do this with 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;
+8
source

, , , . , IF , SQL-. , SELECT CASE.

, - :

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;

, . . : http://dev.mysql.com/doc/refman/5.0/en/select-into-statement.html

0

More articles:


All Articles