user4812000 user4812000 - 7 months ago 23
SQL Question

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

Answer

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