[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 919
  • Last Modified:

Subquery on the same table

Is it possible to do a subquery on the same table?
Example of what I want; A user has a manager. Therefore, the table user contains attributes: id, name, ..., managerId. Is there a (sub)query possibility to select the name of the manager directly when selecting the user?
2 Solutions
A subquery is not needed. you can do a left outer join

Select t1.* t2.name as ManagerName on t1 left outer join t1 as t2 on t1.id=t2.managerId
Philip PinnellCommented:
A join would be simpler

select emp.id, emp.name, mgr.name from table emp
join table mgr
on emp. managerId = mgr.ID
inghfsAuthor Commented:
but t1 = t2 in my case?
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Jagdish DevakuSr DB ArchitectCommented:

As you said that user has a manager... mean every user is mapped to some or other manager...

you can write join between the table...

select * from xyz inner join
abc on xyz.managerid = abc.managerid
where abc.userid = '12345'

i think this will solve the issue...
Philip PinnellCommented:
both hernst and my sugestions are right

you join to the same table name giving it a different alias

then join on the first alias.managerid = to the second alias.id

select EMP.id, EMP.name, MGR.name from STAFFTABLE EMP
left outer join STAFFTABLE MGR
on EMP. managerId = MGR.ID

as hernst says it should be a left outer ( i was a bit lazy before)
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>but t1 = t2 in my case?
"no". the table alias "t2" given to t1 makes it virtually 2 tables.

taking anycrofts query : 
select emp.id, emp.name employee_name, mgr.name manager_name 
from table emp
join table emp mgr
on emp.managerId = mgr.ID 
the alias mgr refers to table emp also, but allows to join to another row.

Open in new window

inghfsAuthor Commented:
Philip PinnellCommented:
These answers only meritted a 'B' ?

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now