Hello!
I have two tables:
My friends: id, user1_id, user2_id, joined
Users (users): id, name, sename

I want to get the value from the user1_id field and user2_id to merge them into one column, and already on this column make INNER JOIN with the users table.

There were no problems with the merger, I do this:

SELECT user1_id as friend_id, joined FROM friends WHERE user2_id = 7 #Выбираем всех друзей пользователя из второй колонки UNION SELECT user2_id, joined FROM friends WHERE user1_id = 7 #Выбираем всех друзей пользователя из первой колонки

But JOIN just can not make friends with the union. I do this:

 SELECT users.*, friends.user1_id as friend_id, friends.joined FROM users INNER JOIN friends ON friends.friend_id = users.id WHERE friends.user2_id = 7 UNION SELECT user2_id, joined FROM friends WHERE user1_id = 7

In a nutshell: User's friends can be in the friends.user1_id column or friends.user2_id, in order to simplify the query, I want to combine these two columns and get the right users from the users table by this column.

    1 answer 1

     SELECT users.*, friend_id, joined FROM( SELECT user1_id as friend_id, joined FROM friends WHERE user2_id = 7 #Выбираем всех друзей пользователя из второй колонки UNION SELECT user2_id, joined FROM friends WHERE user1_id = 7 #Выбираем всех друзей пользователя из первой колонки )friends INNER JOIN users ON friends.friend_id = users.id
    • Thank you very much!!! Everything Works - QWERTY
    • @QWERTY, but where will it go :) - pegoopik