Creating an efficient sql select query.

mg_ · #1 (**permalink**) 01-22-12, 12:34

Hello,

I have a table - Users: | id | name | manager_id |

The manager_id references the User.id,

I need to find an employee by an id or name and this employee has to be a manager to someone else.

The simplest initial idea was the following - do a sub-select and check if the id is present within the manager_id column, but this takes a bit of time.

Code:

SELECT usr.* 
FROM Users usr
WHERE (
 SELECT count(*)
 FROM Users mg
 WHERE mg.manager_id = usr.id AND
 rownum = 1
 ) > 0) AND usr.name = 'John'

Is there a way to, for example, inner join the table to it self leaving only the rows that are managers?

anacedent · #2 (**permalink**) 01-22-12, 12:49

https://forums.oracle.com/forums/thr...36328&tstart=0

mg_ · #3 (**permalink**) 01-22-12, 13:11

Yes, thank you, thought I'd get an input from another forum as well.

Problem still remains.

tcbenkhard · #4 (**permalink**) 01-25-12, 05:32

Quote:

Originally Posted by mg_

Hello,

I have a table - Users: | id | name | manager_id |

The manager_id references the User.id,

I need to find an employee by an id or name and this employee has to be a manager to someone else.

The simplest initial idea was the following - do a sub-select and check if the id is present within the manager_id column, but this takes a bit of time.

Code:

SELECT usr.* 
FROM Users usr
WHERE (
 SELECT count(*)
 FROM Users mg
 WHERE mg.manager_id = usr.id AND
 rownum = 1
 ) > 0) AND usr.name = 'John'

Is there a way to, for example, inner join the table to it self leaving only the rows that are managers?

Code:

select * 
from users 01 where id in (select manager_id from users 02)

OR

Code:

select * from users 01 
join users 02 on 01.id = 02.manager_id

Internet Services

Web Development

Digital Marketing

Consumer Tech