Query data from two tables and substitute foreign keys of first table with values of second There is data table

mysql&gt; select * from data;
+----+--------------------------------------+
| id | uid                                  |
+----+--------------------------------------+
|  1 | 466a5039-c4c1-403d-826d-07ed2399e288 |
|  2 | 188a193f-0638-4b71-9cda-03c30b4e008a |
|  3 | f8ed4eba-635b-4eff-a203-e72a8735fb27 |
|  4 | 7aa235f0-2441-4a00-bc5b-06194609a3d0 |
+----+--------------------------------------+
4 rows in set (0.00 sec)


and relation table

mysql&gt; select * from relation;
+----+---------+----------+
| id | left    | right    |
+----+---------+----------+
|  1 |       1 |        3 |
|  2 |       4 |        2 |
+----+---------+----------+
2 rows in set (0.00 sec)


left and right are foreign keys and both reference data(id)

I need to build query which would return this result

+--------------------------------------+--------------------------------------+
| left_uid                             | right_uid                            |
+--------------------------------------+--------------------------------------+
| 466a5039-c4c1-403d-826d-07ed2399e288 | f8ed4eba-635b-4eff-a203-e72a8735fb27 |
| 7aa235f0-2441-4a00-bc5b-06194609a3d0 | 188a193f-0638-4b71-9cda-03c30b4e008a |
+--------------------------------------+--------------------------------------+
PROPOSED QUERY

SELECT
    IFNULL(B.uid,'') left_uid,
    IFNULL(C.uid,'') right_uid
FROM
    relation A
    LEFT JOIN data B ON A.left  = B.id
    LEFT JOIN data C ON A.right = C.id
;


I did it as LEFT JOIN rather than INNER JOIN in case data is missing from data table

YOUR SAMPLE DATA

drop database if exists gumkins;
create database gumkins;
use gumkins
create table data
(
    id int not null auto_increment,
    uid char(36),
    primary key (id)
);
create table relation
(
    id int not null auto_increment,
    `left` int not null,
    `right` int not null,
    primary key (id)
);
insert into data (uid) values ('466a5039-c4c1-403d-826d-07ed2399e288');
insert into data (uid) values ('188a193f-0638-4b71-9cda-03c30b4e008a');
insert into data (uid) values ('f8ed4eba-635b-4eff-a203-e72a8735fb27');
insert into data (uid) values ('7aa235f0-2441-4a00-bc5b-06194609a3d0');
insert into relation (`left`,`right`) values (1,3);
insert into relation (`left`,`right`) values (4,2);


YOUR SAMPLE DATA EXECUTED

mysql&gt; drop database if exists gumkins;
Query OK, 2 rows affected (0.02 sec)

mysql&gt; create database gumkins;
Query OK, 1 row affected (0.00 sec)

mysql&gt; use gumkins
Database changed
mysql&gt; create table data
    -&gt; (
    -&gt;     id int not null auto_increment,
    -&gt;     uid char(36),
    -&gt;     primary key (id)
    -&gt; );
Query OK, 0 rows affected (0.05 sec)

mysql&gt; create table relation
    -&gt; (
    -&gt;     id int not null auto_increment,
    -&gt;     `left` int not null,
    -&gt;     `right` int not null,
    -&gt;     primary key (id)
    -&gt; );
Query OK, 0 rows affected (0.05 sec)

mysql&gt; insert into data (uid) values ('466a5039-c4c1-403d-826d-07ed2399e288');
Query OK, 1 row affected (0.00 sec)

mysql&gt; insert into data (uid) values ('188a193f-0638-4b71-9cda-03c30b4e008a');
Query OK, 1 row affected (0.00 sec)

mysql&gt; insert into data (uid) values ('f8ed4eba-635b-4eff-a203-e72a8735fb27');
Query OK, 1 row affected (0.00 sec)

mysql&gt; insert into data (uid) values ('7aa235f0-2441-4a00-bc5b-06194609a3d0');
Query OK, 1 row affected (0.01 sec)

mysql&gt; insert into relation (`left`,`right`) values (1,3);
Query OK, 1 row affected (0.00 sec)

mysql&gt; insert into relation (`left`,`right`) values (4,2);
Query OK, 1 row affected (0.00 sec)


YOUR SAMPLE DATA LOADED

mysql&gt; select * from data;
+----+--------------------------------------+
| id | uid                                  |
+----+--------------------------------------+
|  1 | 466a5039-c4c1-403d-826d-07ed2399e288 |
|  2 | 188a193f-0638-4b71-9cda-03c30b4e008a |
|  3 | f8ed4eba-635b-4eff-a203-e72a8735fb27 |
|  4 | 7aa235f0-2441-4a00-bc5b-06194609a3d0 |
+----+--------------------------------------+
4 rows in set (0.00 sec)

mysql&gt; select * from relation;
+----+------+-------+
| id | left | right |
+----+------+-------+
|  1 |    1 |     3 |
|  2 |    4 |     2 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql&gt;


PROPOSED QUERY EXECUTED

mysql&gt; SELECT
    -&gt;     IFNULL(B.uid,'') left_uid,
    -&gt;     IFNULL(C.uid,'') right_uid
    -&gt; FROM
    -&gt;     relation A
    -&gt;     LEFT JOIN data B ON A.left  = B.id
    -&gt;     LEFT JOIN data C ON A.right = C.id
    -&gt; ;
+--------------------------------------+--------------------------------------+
| left_uid                             | right_uid                            |
+--------------------------------------+--------------------------------------+
| 466a5039-c4c1-403d-826d-07ed2399e288 | f8ed4eba-635b-4eff-a203-e72a8735fb27 |
| 7aa235f0-2441-4a00-bc5b-06194609a3d0 | 188a193f-0638-4b71-9cda-03c30b4e008a |
+--------------------------------------+--------------------------------------+
2 rows in set (0.00 sec)

mysql&gt;


GIVE IT A TRY !!!