SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
 Oct  8 2022 05:58:25 
 Copyright (C) 2022 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE mytableA ( 
ID_A INTEGER ,
tbl INTEGER ,
sogg_ID INTEGER );

INSERT INTO  mytableA  VALUES
('55','1','22'),
('55','2','12'),
('7','3','8');


3 rows affected

CREATE TABLE mytableB ( 
ID_B INTEGER ,
Nome_B VARCHAR(50) ,
Cogn_B VARCHAR(50) );

INSERT INTO  mytableB  VALUES
('22','Luca','Rossi'),
('15','Mario','Verdi'),
('8','Antonio','Belli');
3 rows affected

CREATE TABLE mytableC ( 
ID_C INTEGER ,
Nome_C VARCHAR(50) ,
Cogn_C VARCHAR(50) );

INSERT INTO  mytableC  VALUES
('3','Sergio','Bianchi'),
('12','Simone','Ferrari'),
('10','Arianna','Ricci');
3 rows affected

CREATE TABLE mytableD ( 
ID_D INTEGER ,
Nome_D VARCHAR(50) ,
Cogn_D VARCHAR(50) );

INSERT INTO  mytableD  VALUES
('21','Giulio','Sala'),
('7','Sofia','Russo'),
('14','Luca','Viola');
3 rows affected

SELECT
  *
FROM
  mytableA
LEFT JOIN
(
  SELECT 1 AS tbl, id_b AS id, nome_b AS nome, cogn_b AS cogn
    FROM myTableB
  UNION ALL
  SELECT 2 AS tbl, id_c AS id, nome_c AS nome, cogn_c AS cogn
    FROM myTableC
  UNION ALL
  SELECT 3 AS tbl, id_d AS id, nome_d AS nome, cogn_d AS cogn
    FROM myTableD
)
  AS myTableBCD
    ON  myTableBCD.tbl = myTableA.tbl
    AND MyTableBCD.ID  = myTableA.sogg_id
WHERE
  myTableA.id_a = 55
;


ID_A
tbl
sogg_ID
tbl
id
nome
cogn


55
1
22
1
22
Luca
Rossi

55
2
12
2
12
Simone
Ferrari