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

       @use := IF(first_id = 25, second_id, first_id)
FROM   contacts
       INNER JOIN user
               ON @use =
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 |   |                                        23 |
| 23 | |                                        24 |
| 24 |          |                                        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.

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

Leave a Reply