Q:

How to use 2 instances of the same table

I am wondering how to use 2 instances of the same table in the following example , I know how to do it but I just cant make it work for my task.
I have the following tables :

Agency(id_agency,name)
Space(id_space,address)
Offer(id_agency,id_space)

Task: Find out the address,agency name 1 ,agency name 2 for spaces offered by two different agencies(the combination of 2 agencies is unique).
What I tried:
1)
SELECT ADDRESS,A.NAME,B.NAME
FROM AGENCY A 
    INNER JOIN OFFER O ON A.ID_AGENCY=O.ID_AGENCY
    INNER JOIN SPACE S ON O.ID_SPACE=S.ID_SPACE
WHERE S.ID_SPACE=ANY(SELECT S.ID_SPACE FROM AGENCY B
    INNER JOIN OFFER O ON B.ID_AGENCY=O.ID_AGENCY 
    INNER JOIN SPACE S ON O.ID_SPACE=S.ID_SPACE
);

2)
SELECT ADDRESS
FROM AGENCY A 
INNER JOIN OFFER O ON A.ID_AGENCY=O.ID_AGENCY
INNER JOIN SPACE S ON O.ID_SPACE=S.ID_SPACE
        INTERSECT
SELECT ADDRESS
FROM AGENCY B 
INNER JOIN OFFER O ON B.ID_AGENCY=O.ID_AGENCY
INNER JOIN SPACE S ON O.ID_SPACE=S.ID_SPACE
WHERE A.ID_AGENCY<>B.ID_AGENCY;

In the second example I have no idea how to make it show A.name and b.name, since intersect won't work if I try to add them...
I tried to do this for the last 3 hours , sadly, I guess I can`t do it with the skills I have so far. :(
Thanks in advance
Edit 1: I hope you understand, thats how it should look.
Agency
id_agency  name
---------- -------
1          Agency1
2          Agency2
3          Agency3

Space
id_space  address
--------- --------
1         address1
2         address2
3         address3

Offer
id_agency   id_space
----------- --------
1           1
2           1
3           2

Expected output:
Address     Name1    Name2
----------- -------- -------
address1    Agency1  Agency2

A:

To have the results in 1 row, each pair of agencies per row, as you asked for:
select S.address as address, A1.name as agency_1, A2.name as agency_2
from offer O1
    join offer O2
        on O2.id_space = O1.id_space
        and O2.id_agency != O1.id_agency
    join space S
        on S.id_space = O1.id_space
    join agency A1
        on A1.id_agency = O1.id_agency
    join agency A2
        on A2.id_agency = O2.id_agency
;

The "core functionality" here is the join of offer no.1 (O1 alias) to offer no.2 (O2 alias) on equality of id_space but difference of id_agency.
An interesting exercise: To have the results in multiple rows, one agency per row:
select S.address, A.name as agency, X.number_of_agencies_per_space
from (
        select id_space, id_agency, count(1) over (partition by id_space) as number_of_agencies_per_space
        from offer
    ) X
    join space S
        on S.id_space = X.id_space
    join agency A
        on A.id_agency = X.id_agency
where X.number_of_agencies_per_space > 1
;

