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

Questions

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

June 20, 2023

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