Solved

Subquery on the same table

Posted on 2008-06-23
8
909 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 250 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 250 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 Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

828 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