Get attribute for a returned value

user_2708215 0
Viewed 23

So, I'm diving into SQL, and I'm having a hard time getting finding an answer to this. I mention that because I really have 2 questions: (1) What is what I'm doing called? (2) How do I do it? I ask (1) in order to be more self-sufficient, but I don't even know what to google.

Now, here's the deal: Say there are 2 tables, 'Account' and 'Employees'. Accounts are clients of the company. Employees are, of course, employees who service accounts. The tables have this simplified structure:

Accounts:

  • ID (Key)
  • Owner ID (An employee, foreign key to the Employee table)
  • Name
  • (other stuff...)

Employee:

  • ID (Key)
  • Manager's ID (The manager is also an employee, of course...)
  • State of Residence
  • Name
  • (other stuff...)

So, for every account, I want:

  • The assigned employee's ID
  • The assigned employee's manager's name
  • The assigned employee's manager's state of residence

The first two of those are straightforward:

SELECT  account.id, 
        account.ownerid, 
        employee.managername,
        <something magical here!!!>
FROM    account
        JOIN
        employee ON account.ownerid = employee.id

But how about the last one, the manager's state of residence? This seems like a subquery, but I haven't been able to figure it out, or even figure out how to phrase the question. Does this have a name? How is it done? Thank you.

sql
1 Answers

No magic is needed, simply assign a second emplyee table to the mix and now you can access alle column from all tables join, as you have two employee tables now, you need to assign alias to differentiate between them

SELECT  account.id, 
        account.ownerid, 
        e1.id
        e2.name managername,
        e2.State_of_ Residence
FROM    account
        JOIN
        employee e1 ON account.ownerid = e1.id
        JOIN
        employee e2 ON e1.Manager_ID= e2.id
user_51935360
Related