Rails SQL INTERSECT not working

Question

I want to get the mutual friends of a user, i.e people that I follow and that follow me back:

# get users I'm following
following_ids = "SELECT followed_id FROM relationships WHERE follower_id = #{self.id}"

# get user that are following me
follower_ids = "SELECT follower_id FROM relationships WHERE followed_id = #{self.id}"

User.where("id IN (#{following_ids} INTERSECT #{follower_ids})")

However, I get the following error:

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERSECT SELECT follower_id FROM relationships WHERE followed_id = 2))' at line 1: SELECT users.* FROM users WHERE (id IN (SELECT followed_id FROM relationships WHERE follower_id = 2 INTERSECT SELECT follower_id FROM relationships WHERE followed_id = 2))

The individual queries do work, so I know it's a problem with the INTERSECT keyword.

Answer 1

MySQL doesn't have INTERSECT. See here, here or here for more information.