postgres获取数据，其中两列引用相同的父ID

Question

postgres 数据库中有 3 个表，

CREATE TABLE tab_name
(
  name_id integer NOT NULL,
  cust_name character varying NOT NULL, -- contains names like david,jones,athur
  CONSTRAINT tab_name_pkey PRIMARY KEY (name_id)
)

CREATE TABLE tab_rel
(
  rel_id integer NOT NULL,
  rel_desc character varying NOT NULL,-- contains relation description father son, sister brother
  CONSTRAINT tab_rel_pkey PRIMARY KEY (rel_id)
)

CREATE TABLE tab_rel_map
(
  rel_id integer NOT NULL,
  name_id1 integer NOT NULL,
  name_id2 integer NOT NULL,
  CONSTRAINT tab_rel_map_name_id1_fkey FOREIGN KEY (name_id1)
      REFERENCES tab_name (name_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tab_rel_map_name_id2_fkey FOREIGN KEY (name_id2)
      REFERENCES tab_name (name_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tab_rel_map_rel_id_fkey FOREIGN KEY (rel_id)
      REFERENCES tab_rel (rel_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

我正在尝试编写以 rel_id 作为输入的函数，并应输出相对于 name_id1 和 name_id2 的 cust_name 。因为 name_id1 和 name_id2 都引用相同的父 ID，所以我无法获取各自的名称。

rel_id | relation     | cust_name1 | cust_name2
------------------------------------------------
1      | Father son   | David      | Jones

Answer 1

您需要两次加入同一个表 - 这是使用 别名 执行此操作的方法：

select 
    rm.rel_id,
    r.rel_desc as relation,
    n1.cust_name as cust_name1,
    n2.cust_name as cust_name2
from tab_rel_map rm
join tab_rel r on r.id = rm.rel_id
left join tab_name n1 on n1.name_id = rm.name_id1
left join tab_name n2 on n2.name_id = rm.name_id2
where rm.rel_id = 1;