USE tempdb;

CREATE TABLE dbo.Albergo(
Codalb int NOT NULL,
CodComune int NOT NULL,
Descrizione varchar(250) NULL,
Citta varchar(50) NULL
CONSTRAINT PK_Albergo PRIMARY KEY(Codalb, CodComune)
);

CREATE TABLE dbo.Dependace(
Coddep int NOT NULL,
CodComune int NOT NULL,
Descrizione varchar(250) NULL,
Citta varchar(50) NULL
CONSTRAINT PK_Dependance PRIMARY KEY(Coddep, CodComune)
);

INSERT dbo.albergo VALUES
      (1, 60038, 'ALBERGO PROVA', 'FROSINONE')
    , (2, 60038, 'ALBERGO PROVA1', 'FROSINONE')
    , (3, 60038, 'ALBERGO PROVA2', 'FROSINONE')
    , (1, 60066, 'ALBERGO PROVA3', 'FROSINONE')
    , (2, 60066, 'ALBERGO PROVA4', 'FROSINONE');

INSERT dbo.Dependace VALUES
      (1, 60038, 'ALBERGO PROVA dependance', 'FROSINONE')
    , (1, 60066, 'ALBERGO PROVA3 dependance', 'FROSINONE');


SELECT 
      A.*
    , D.Coddep
    , D.CodComune AS CodComuneDep
    , D.Descrizione
    
FROM dbo.Albergo AS A
LEFT JOIN dbo.Dependace AS D
ON A.CodComune = D.CodComune
AND A.Descrizione = SUBSTRING(D.Descrizione, 1, PATINDEX('%dependance', D.Descrizione) - 2)
ORDER BY A.CodComune, A.Codalb;

/* Output:

Codalb      CodComune   Descrizione     Citta      Coddep      CodComuneDep Descrizione
----------- ----------- --------------- ---------- ----------- ------------ --------------------------
1           60038       ALBERGO PROVA   FROSINONE  1           60038        ALBERGO PROVA dependance
2           60038       ALBERGO PROVA1  FROSINONE  NULL        NULL         NULL
3           60038       ALBERGO PROVA2  FROSINONE  NULL        NULL         NULL
1           60066       ALBERGO PROVA3  FROSINONE  1           60066        ALBERGO PROVA3 dependance
2           60066       ALBERGO PROVA4  FROSINONE  NULL        NULL         NULL

(5 row(s) affected)

*/

DROP TABLE dbo.Albergo, dbo.Dependace;