英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论