Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Subquery on the same table

Posted on 2008-06-23
8
Medium Priority
?
913 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:Philip Pinnell
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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:Philip Pinnell
Philip Pinnell 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:Philip Pinnell
ID: 21846433
These answers only meritted a 'B' ?
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

721 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