Select unique value pairs based on two participants that occur more than once inside a table We are using MySQL where we have deals table that contains references to two users stored in two columns User_id1 &amp; User_id2. There is no guaranty in which order those are stored. For example the records below represent a deal between two unique pair of users

id ... User_id1 User_id2
-- --- -------- --------
 1 ...       11       20
 2 ...       20       11
 3 ...      116     3459
 4 ...       20       11


We need an SQL query to migrate this data into two new tables below

Participants

chat_id user_id
------- -------
      1      11
      1      20
      2     116
      2    3459


Chat_rooms

id
--
 1
 2


For each unique pair of users in deals table, we need to create a unique conversation record in Chat_rooms table and add those two users as participants to the Participants table with the reference to the created Chat_room ID

I'm hopping to do this with SQL, potentially using some temporary tables.
CREATE VIEW uniqueness (id, User_id1, User_id2)
AS
SELECT MIN(id), 
       LEAST(User_id1, User_id2) User_id1,  
       GREATEST(User_id1, User_id2) User_id2
GROUP BY LEAST(User_id1, User_id2),  
         GREATEST(User_id1, User_id2);


and then

INSERT INTO Chat_rooms (id) SELECT id FROM uniqueness;


and

INSERT INTO Participants (chat_id, user_id)
SELECT id, User_id1 FROM uniqueness
UNION ALL
SELECT id, User_id2 FROM uniqueness;




You may create temporary table instead of view

-- or --

You may insert view's source text into INSERT queries