CODEWITHSUNDEEP
mysql
alex
alex

Reputation: 3

How to check if value exists in one of the columns and in which one in MySQL?

I am trying to create a function in MySQL for my 'friendships' table where I have 4 columns:

friendship_id, status, user1_id and user2_id (last two are also foreign keys which point to another table 'users').

Function should receive one parameter which is users id and return how many friends that user has.

I created a function like this:

CREATE FUNCTION num_of_friends(id int) 
RETURNS int(11)
BEGIN

SET @friends_count = (SELECT COUNT(user2_id) FROM friendships WHERE user1_id = id);
RETURN @friends_count;
END

So this function returns number of friends for that specific user, if his id is in column user1_id.

But there are some users whose id is only in the column user2_id.

How could I check if the user's id which we passed as parameter exists in either of the columns and count friendships based on that?

Upvotes: 0

Views: 490

Answers (1)

Abhishek Ginani
Abhishek Ginani

Reputation: 4751

You can compare the id value with both the columns with OR

CREATE FUNCTION num_of_friends(id int) 
RETURNS int(11)
BEGIN

SET @friends_count = (SELECT COUNT(*) from (select * from friendship  where user1_id=id
                      union
                      select user2_id,user1_id from friendship where user2_id=id)data);
RETURN @friends_count;
END

Check this SQL fiddle: http://sqlfiddle.com/#!9/c5928ca/8

Upvotes: 1

Related Questions