我在想是否可以使用一个Access的 SQL查询，通过一个表中的一个值来确定另一个表中某列的取值。

例如，我有表A、B、C和 D：

A表：

B表：

C表：

D表：

A表的列tbl值 1指向表B，2指向表C，3指向表D。

我需要一个SQL查询，当我选择一个 ID_A 时，能够根据表A 的tbl值，从表B、C、D中返回Nome和 Cogn。

WHERE ID_A=55 的查询结果应该是：

解决方案

Having three tables like that is a poor design choice. They should just be one table, with an extra identification column (tbl).

You can simulate that by unioning the three tables within a subquery.

(Demo below adapted from @luuk's effort.)

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

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

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');

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');

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');

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
;

fiddle

备选方案

Suggesting that SELECT sogg_ID FROM tableA where ID_A=targetIdis quite simple (take few row from tableA), we can process each table B,C,D separately an then use UNION ALL.

Access query with parameter

PARAMETERS targetId long;
SELECT 'B' as tbl, ID_B as sogg_ID,Nome_B    as NomeN,Cogn_B as CognN FROM tableB
WHERE ID_B IN(SELECT sogg_ID FROM tableA where ID_A=targetId)
UNION ALL
SELECT 'C' as tbl, ID_C as sogg_ID, Nome_C   as NomeN, Cogn_C as CognN  FROM tableC
WHERE ID_C IN(SELECT sogg_ID FROM tableA where ID_A=targetId)
UNION ALL
SELECT 'D' as tbl, ID_D as sogg_ID, Nome_D as NomeN, Cogn_D as CognN  FROM tableD
WHERE ID_D IN(SELECT sogg_ID FROM tableA where ID_A=targetId)

Or direct pass ID

SELECT 'B' as tbl, ID_B as sogg_ID,Nome_B    as NomeN,Cogn_B as CognN FROM tableB
WHERE ID_B IN(SELECT sogg_ID FROM tableA where ID_A=55)
UNION ALL
SELECT 'C' as tbl, ID_C as sogg_ID, Nome_C   as NomeN, Cogn_C as CognN  FROM tableC
WHERE ID_C IN(SELECT sogg_ID FROM tableA where ID_A=55)
UNION ALL
SELECT 'D' as tbl, ID_D as sogg_ID, Nome_D as NomeN, Cogn_D as CognN  FROM tableD
WHERE ID_D IN(SELECT sogg_ID FROM tableA where ID_A=55)