当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Cannot create nested query in teradata

问题描述:

I am working with 2 tables to get the columns: Name, Manager Name, and Close date from my list of opportunities.

The Opportunity table contains: USER ID and Close Date

The User table contains: USER ID, Name and Manager ID (the managers are also listed in this table)

The below query returns the manager ID rather than the name. How do I use the Manager ID returned in my main query to query the user table for the manager's name?

SELECT

OPPORTUNITY.ID,

USER.Name,

OPPORTUNITY.CloseDate,

USER.ManagerID

FROM

OPPORTUNITY

JOIN

USER

ON

USER.ID=OPPORTUNITY.ID

网友答案:

Try a self join of another user table:

SELECT  
    o.ID,
    u1.Name, 
    o.CloseDate,
    u2.Name As ManagerName,
FROM OPPORTUNITY  o
INNER JOIN USER u1
   ON u1.ID = o.ID
INNER JOIN USER u2
   ON u2.ID = u1.ManagerID
分享给朋友:
您可能感兴趣的文章:
随机阅读: