I have the following structure:
TABLE1 TABLE2
id_worker_main id_worker
id_worker_sub name_worker
I need to make a select getting the name_worker for both id_worker_main and id_worker_sub, but I can't. The following query return the same name twice.
SELECT t2.name_worker as main, t2.name_worker as sub
FROM table1
INNER JOIN table2 ON t1.id_worker_main = t2.id_worker AND t1.id_worker_sub = t2.id_worker
You have to join with the same table twice. Just use a different alias for each join
With this workers
INSERT INTO Table2
([id_worker], [name_worker])
VALUES
(1, 'Juan'),
(2, 'Angela'),
(3, 'Peter');
And this relationship
INSERT INTO Table1
([id_worker_main], [id_worker_sub])
VALUES
(1, 2),
(2, 3),
(1, 3);
Use this query
SELECT A.name_worker MAINWORKER, B.name_worker SUBWORKER
FROM Table1 T1
Inner join Table2 A
ON T1.id_worker_main = A.id_worker
Inner join Table2 B
ON T1.id_worker_sub = B.id_worker
OUTPUT
| MAINWORKER | SUBWORKER |
|------------|-----------|
| Juan | Angela |
| Angela | Peter |
| Juan | Peter |
If I understood your problem correctly, you can join table2 multiple times
SELECT t21.name_worker as main, t22.name_worker as sub
FROM table1 t1
JOIN table2 t21 on t21.id_worker = t1.id_worker_main
JOIN table2 t22 on t22.id_worker = t1.id_worker_sub
