postgresql:连接2个select语句时出现连接语法错误

3duebb1j  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(426)

我正在clinicaltrials.gov上查询aact数据库。数据库模型就在这里:https://aact.ctti-clinicaltrials.org/schema. 我有两个我正在选择的模式(ctgov,proj\u cdek\u standard\u orgs)。我想加入两个select语句。编辑:我现在已经试过给我的子查询取别名,但是仍然没有效果。我得到以下错误:

(SELECT ctgov.sponsors.name, ctgov.sponsors.nct_id, ctgov.sponsors.id, ctgov.studies.phase
FROM ctgov.sponsors, ctgov.studies
WHERE ctgov.sponsors.nct_id=ctgov.studies.nct_id) A

FULL [OUTER] JOIN

(SELECT proj_cdek_standard_orgs.cdek_synonyms.id, proj_cdek_standard_orgs.cdek_synonyms.name
FROM proj_cdek_standard_orgs.cdek_synonyms) B

ON

A.name = B.name;

我自己可以很好地处理这两个select语句,但是我尝试了查询,却得到了以下错误: ERROR: syntax error at or near "t1" LINE 7: ) t1 我做错了什么?如何使用联接而不出现语法错误?

unhi4e5o

unhi4e5o1#

请使用下面的查询,

SELECT ctgov.sponsors.name, ctgov.sponsors.nct_id, ctgov.sponsors.id, 
    ctgov.studies.phase, proj_cdek_standard_orgs.cdek_synonyms.id, 
    proj_cdek_standard_orgs.cdek_synonyms.name
FROM ctgov.sponsors, ctgov.studies, proj_cdek_standard_orgs.cdek_synonyms
WHERE ctgov.sponsors.nct_id=ctgov.studies.nct_id
and proj_cdek_standard_orgs.cdek_synonyms.name = ctgov.sponsors.name;

但正确的方法是使用传统连接,

SELECT ctgov.sponsors.name, ctgov.sponsors.nct_id, ctgov.sponsors.id, 
    ctgov.studies.phase, proj_cdek_standard_orgs.cdek_synonyms.id, 
    proj_cdek_standard_orgs.cdek_synonyms.name
FROM ctgov.sponsors
INNER JOIN ctgov.studies
ON (ctgov.sponsors.nct_id=ctgov.studies.nct_id)
INNER  JOIN proj_cdek_standard_orgs.cdek_synonyms
ON (proj_cdek_standard_orgs.cdek_synonyms.name = ctgov.sponsors.name);

您可以根据您的要求更改为左或全外接。

acruukt9

acruukt92#

必须为子查询提供别名。另外,您不应该像在第一个子查询中那样使用隐式联接,始终尝试使用显式联接。

SELECT 
    *
FROM
(
  SELECT 
    ctgov.sponsors.name, ctgov.sponsors.nct_id, ctgov.sponsors.id, ctgov.studies.phase
  FROM ctgov.sponsors
  JOIN ctgov.studies
  ON ctgov.sponsors.nct_id=ctgov.studies.nct_id
) t1

FULL JOIN

(
  SELECT 
    proj_cdek_standard_orgs.cdek_synonyms.id, proj_cdek_standard_orgs.cdek_synonyms.name
  FROM proj_cdek_standard_orgs.cdek_synonyms
) t2

ON

t1.name = t2.name;

相关问题