Mysql Query using Variable and Inner Join is not returning the correct result

I am quite new to MySQL and i wanted to write a query for my MariaDB database. I have a user and a Contacts table. The contacts table contains two foreign keys and the user table contains an email and a user id. What i want to do is return the emails and the id of all contacts of a given user Id. I used the variable because the id of the user we are searching for can be the first or second id in a contact relation.

The following

SELECT id,
       email,
       @use := IF(first_id = 25, second_id, first_id)
FROM   contacts
       INNER JOIN user
               ON @use = user.id
WHERE  first_id = 25
        OR second_id = 25;

25 is the user id in this case and this query is returning the following result.

+----+-------------------------+-------------------------------------------+
| id | email                   | @use:= IF(first_id=25,second_id,first_id) |
+----+-------------------------+-------------------------------------------+
| 23 | secretemail@gmail.com   |                                        23 |
| 23 | best.secretem@yahoo.com |                                        24 |
| 24 | wow.spa@web.de          |                                        23 |
+----+-------------------------+-------------------------------------------+

What confusing to me is, that the ids do not match when using the inner join. Thanks in advance!

>Solution :

You can use IN () to match with multiple columns.

SELECT u.id,
       u.email
FROM contacts AS c
INNER JOIN user AS u ON user.id IN (c.first_id, c.second_id)
WHERE 25 IN (c.first_id, c.second_id) 
  AND u.id != 25 -- don't return the user whose contact are being listed

Leave a Reply