如何在已有多表关联的SQL查询中新增关联MTMPTVGEN表获取MZS_DPAETAST字段

最终修改完成的查询语句 #

SELECT 
  ARTICLE_MODE.GA_CODEARTICLE, 
  CC1.CC_LIBELLE AS C1, 
  YX2.YX_LIBELLE AS C2, 
  YX3.YX_LIBELLE AS C3, 
  ARTICLE_MODE.GA_LIBELLE, 
  CC4.CC_LIBELLE AS C5, 
  CC5.CC_LIBELLE AS C6,
  CC6.CC_LIBELLE AS C15,
  ARTICLE_MODE.GA_LIBREART3,
  ARTICLE_MODE.GA_LIBREART5,
  -- 新增需要查询的MTMPTVGEN表字段
  MTMPTVGEN.MZS_DPAETAST
FROM ARTICLE_MODE  
LEFT OUTER JOIN PGI_LOOKUP(GCFAMILLENIV1) CC1 ON GA_FAMILLENIV1=CC1.CC_CODE 
AND CC1.CC_TYPE="FN1"   
LEFT OUTER JOIN PGI_LOOKUP(GCLIBREART1) YX2 ON GA_LIBREART1=YX2.YX_CODE 
AND YX2.YX_TYPE="LA1"   
LEFT OUTER JOIN PGI_LOOKUP(GCLIBREART2) YX3 ON GA_LIBREART2=YX3.YX_CODE 
AND YX3.YX_TYPE="LA2"   
LEFT OUTER JOIN PGI_LOOKUP(GCCOLLECTION) CC4 ON GA_COLLECTION=CC4.CC_CODE 
AND CC4.CC_TYPE="GCO"  
LEFT OUTER JOIN PGI_LOOKUP(GCFAMILLENIV2) CC5 ON GA_FAMILLENIV2=CC5.CC_CODE 
AND CC5.CC_TYPE="FN2"
LEFT OUTER JOIN PGI_LOOKUP(GCFAMILLENIV5) CC6 ON GA2_FAMILLENIV5=CC6.CC_CODE 
AND CC6.CC_TYPE="FN5"
-- 新增关联MTMPTVGEN表的逻辑，保留ARTICLE_MODE所有符合条件的记录，没有匹配的MZS_DPAETAST会返回NULL
LEFT OUTER JOIN MTMPTVGEN ON ARTICLE_MODE.GA_CODEARTICLE = MTMPTVGEN.MZS_ARTICLE
WHERE (GA_EMBALLAGE<>"X" 
AND (GA_TYPEARTICLE NOT IN ("PRE","FI","FRA","UL","PAC"))
AND ((GA_STATUTART="GEN")))  
ORDER BY GA_DATEMODIF DESC

修改说明 #

• 在SELECT字段列表最后新增了MTMPTVGEN.MZS_DPAETAST字段，用来输出你需要的额外信息
• 在原有所有JOIN语句之后新增了LEFT OUTER JOIN MTMPTVGEN的关联逻辑，关联条件为两个表的编码字段相等，这样不会影响你原有查询返回的ARTICLE_MODE相关记录，没有匹配到的MTMPTVGEN记录对应的MZS_DPAETAST字段会返回NULL
• 如果存在同一个GA_CODEARTICLE对应多条MTMPTVGEN记录的情况，查询结果行数会增加，如果需要去重可以根据实际业务需求添加DISTINCT或者GROUP BY逻辑。

内容的提问来源于stack exchange，提问作者Eric27