1个表上的2列具有相同的连接。

huangapple go评论57阅读模式
英文:

2 columns on 1 table that have same join

问题

以下是翻译好的部分:

"New to this so bear with me.
I have a 2 tables
Table 1 has SupplierNumber and AltSupplierNo

I need to join both of these columns to Table2 to bring back the supplier names twice. Once beside SupplierNumber and again beside AltSupplierNumber

I cant get this to work
thanks all"

代码部分未包含在翻译范围内。如果您有任何其他需要翻译的内容,请提供并我将为您进行翻译。

英文:

New to this so bear with me.
I have a 2 tables
Table 1 has SupplierNumber and AltSupplierNo

I need to join both of these columns to Table2 to bring back the supplier names twice. Once beside SupplierNumber and again beside AltSupplierNumber

I cant get this to work
thanks all

Code Below

SELECT 
    PDDOCO as OrderNumber, 
    PDMCU as Branch,
    PDAN8 as SupplierNumber,   (first supplier number)
    ABALPH as SupplierName,
    PDANCR as AltSupplierNo,   (Alternate supplier number)
    ABALPH as AltSupplierName,
FROM system.Table1
JOIN system.Table2 ON system.Table1.PDAN8=system.Table2.Supplier
WHERE PDUOPN > 0;

答案1

得分: 1

当连接多个表格,特别是当需要多次连接单个表格时,请使用表格别名,以便您可以指定从哪个表格获取列。在查询中对所有表格都这样做,并在每个列引用前加上适当的别名。

SELECT
      t.PDDOCO AS 订单号
    , t.PDMCU AS 分支
    , t.PDAN8 AS 供应商编号 --(第一个供应商编号)
    , s1.ABALPH AS 供应商名称
    , t.PDANCR AS 替代供应商号码 --(替代供应商编号)
    , s2.ABALPH AS 替代供应商名称
FROM system.Table1 AS t
    INNER JOIN system.Table2 AS s1 ON t.PDAN8 = s1.Supplier
    LEFT JOIN system.Table2 AS s2 ON t.PDANCR = s2.Supplier
WHERE t.PDUOPN > 0

注意:我假设某些行可能没有替代供应商,因此第二个连接使用了左连接。

此外:上面所有的列引用都是猜测的,有些可能是错误的。

英文:

When joining multiple tables, and in particular when needed to join a single table more then once: use table aliases so that you can specify which tabe the columns columns are to come from. Do this for all tables in the query, and prefix every column reference with the approriate alias.

SELECT
      t.PDDOCO AS OrderNumber
    , t.PDMCU AS Branch
    , t.PDAN8 AS SupplierNumber -- (first supplier number) 
    , s1.ABALPH AS SupplierName
    , t.PDANCR AS AltSupplierNo -- (Alternate supplier number) 
    , s2.ABALPH AS AltSupplierName
FROM system.Table1 AS t
    INNER JOIN system.Table2 AS s1 ON t.PDAN8 = s1.Supplier
    LEFT JOIN system.Table2 AS s2 ON t.PDANCR = s2.Supplier
WHERE t.PDUOPN > 0

Note: I have assumed that some rows might not have an alternative supplier, hence the second join uses a left join.

Also: all the column references above are a guess, some may be wrong

huangapple
  • 本文由 发表于 2023年6月16日 11:24:51
  • 转载请务必保留本文链接:https://go.coder-hub.com/76486783.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定