Improve company productivity with a Business Account.Sign Up

x
?
Solved

Subquery on the same table

Posted on 2008-06-23
8
Medium Priority
?
924 Views
Last Modified: 2010-04-21
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?
0
Comment
Question by:inghfs
8 Comments
 
LVL 48

Accepted Solution

by:
hernst42 earned 750 total points
ID: 21844735
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
0
 
LVL 13

Expert Comment

by:Atdhe Nuhiu
ID: 21844741
A join would be simpler

select emp.id, emp.name, mgr.name from table emp
join table mgr
on emp. managerId = mgr.ID
0
 

Author Comment

by:inghfs
ID: 21844856
but t1 = t2 in my case?
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 21844976
Hi,

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...
0
 
LVL 13

Assisted Solution

by:Atdhe Nuhiu
Atdhe Nuhiu earned 750 total points
ID: 21845101
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)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21845243
>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

0
 

Author Closing Comment

by:inghfs
ID: 31469674
thanks
0
 
LVL 13

Expert Comment

by:Atdhe Nuhiu
ID: 21846433
These answers only meritted a 'B' ?
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Ready to get certified? Check out some courses that help you prepare for third-party exams.
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question