Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: Creating an efficient sql select query.

    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?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2012
    Posts
    2
    Yes, thank you, thought I'd get an input from another forum as well.

    Problem still remains.

  4. #4
    Join Date
    Jan 2011
    Posts
    8
    Quote Originally Posted by mg_ View Post
    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
    Last edited by tcbenkhard; 01-25-12 at 07:09.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •