不存在的postgreSQL

Question

我有5张桌子

psa_aso_target  psa_aso_branch    psa_aso_targetdetails  psa_aso_dept
--------------  ---------------   ---------------------  ------------
target_id[PK]   branch_id[PK]     target_id[FK]          dept_id[PK]
branch_id[FK]   branch_desc[FK]   dept_id[FK]            dept_desc
                                  grade_id[FK]

psa_aso_grade
-------------
grade_id
grade_code

这是我的表中的示例数据

psa_aso_branch  psa_aso_dept psa_aso_grade  psa_aso_target
b1 | Jakarta    d1 | Dept1   g1 | 101       t1 | b2
b2 | Surabaya   d2 | Dept2   g2 | 201       t2 | b3
b3 | Semarang   d3 | Dept3   g3 | 301
                             g4 | 401
                             g5 | 501

psa_aso_targetdetails
t1 | d1 | g1    t1 | d2 | g1    t2 | d3 | g3                        
t1 | d1 | g2    t1 | d2 | g2    t2 | d3 | g4                   
t1 | d1 | g3    t1 | d2 | g3                 
t1 | d1 | g4    t2 | d1 | g1                  
t1 | d1 | g5    t2 | d1 | g4                

我需要什么查询才能得到这个预期的结果

branch_id | branch_desc | dept_id | dept_desc | string_agg (grade_code)
-----------------------------------------------
b1        | Jakarta     | d1      | dept1     | g1,g2,g3,g4,g5
b1        | Jakarta     | d1      | dept2     | g1,g2,g3,g4,g5
b1        | Jakarta     | d1      | dept3     | g1,g2,g3,g4,g5 
b2        | Surabaya    | d2      | dept2     | g4,g5
b2        | Surabaya    | d2      | dept3     | g1,g2,g3,g4,g5
b3        | Semarang    | d2      | dept3     | g2,g3,g5
b3        | Semarang    | d2      | dept3     | g1,g2,g3,g4,g5
b3        | Semarang    | d2      | dept3     | g1,g2,g5

解释是:我想要选择所有具有尚未在psa_aso_targetdetails上挑选等级的分支和部门(在分支和部门上)，以便输出在上面

Answer 1

尝试：

select t1.branch_id,t1.branch_desc,t4.dept_id, t4.dept_desc, string_agg (grade_code)
from psa_aso_branch t1
join psa_aso_target t2 on t1.branch_id = t2.branch_id
join psa_aso_targetdetails t3 on on t3.target_id = t1.target_id
join psa_aso_dept t4 on t4.dept_id = t3.dept_id
join psa_aso_grade t5 on t5.grade_id = t3.grade_id
group by t1.branch_id,t1.branch_desc,t4.dept_id, t4.dept_desc