Professional Web Applications Themes

Need Help With a SQL Statement - Coldfusion Database Access

I've created an application that collects performance reviews for the employees in our organization. The database contains a "people" table which contains the usual ID, FirstName LastName, etc. It also has a field for "supervisorID" which contains the ID of the supervisor from the same "people" table. I need to be able to list the supervisors by name and their direct reports, but I can't get by the SQL statement. If the supervisor info was in another table I would do an inner join on the SupervisorID field but it's in the same table. Help!...

  1. #1

    Default Need Help With a SQL Statement

    I've created an application that collects performance reviews for the employees
    in our organization. The database contains a "people" table which contains the
    usual ID, FirstName LastName, etc. It also has a field for "supervisorID"
    which contains the ID of the supervisor from the same "people" table. I need
    to be able to list the supervisors by name and their direct reports, but I
    can't get by the SQL statement. If the supervisor info was in another table I
    would do an inner join on the SupervisorID field but it's in the same table.
    Help!

    mpc Guest

  2. #2

    Default Re: Need Help With a SQL Statement

    Not sure which db u r using but self join would handle this.
    CFDEBUG Guest

  3. #3

    Default Re: Need Help With a SQL Statement

    Qyery could be like this:
    SELECT
    (p1.last_name, p1.first_name) as supervisor,
    (p2.last_name, p2.first_name) as employee
    FROM peopl p1,
    people p2
    WHERE
    p2.supervisorID = p1.employeeID
    ORDER BY supervisor, employee

    In query output use group="supervisor".

    CF_Oracle Guest

  4. #4

    Default Re: Need Help With a SQL Statement

    Thanks for your help! Self Joins are news to me. (I'm a sql newbie!)

    Here's what I put together from your suggestions:

    SELECT e.lastname as eLN, e.firstname as eFN, e.id as eID, s.lastname as sLN,
    s.firstname as sFN, s.id as sID, e.supervisor as Esupervisor
    FROM people e, people s
    WHERE e.supervisor = s.ID
    Group By sln, eLN

    It doesn't like the group by line. I keep getting the message "You tried to
    execute a query that does not include the specified expression 'eLN' as part of
    an aggregate function."

    Any suggestions?

    mpc Guest

  5. #5

    Default Re: Need Help With a SQL Statement

    Use ODER BY (GROUP by requires some aggregate like sum, max, count that you
    don't need here.

    Then put <cfoutput query ="queryname" group="Esupervisor
    ">
    <td>#queryname.sLN >#queryname.sFLN#</td>
    <td>#queryname.eLN >#queryname.eFLN#</td>
    </cfoutput>

    CF_Oracle Guest

Similar Threads

  1. help on SQL statement
    By filippo2991@virgilio.it in forum MySQL
    Replies: 2
    Last Post: June 9th, 01:13 PM
  2. [PHP] SQL statement
    By Chris W. Parker in forum PHP Development
    Replies: 14
    Last Post: September 28th, 03:48 PM
  3. if statement with url
    By John in forum PHP Development
    Replies: 5
    Last Post: September 11th, 03:13 AM
  4. if statement
    By alexz in forum ASP
    Replies: 8
    Last Post: July 15th, 04:57 PM
  5. Can i say OR in an if/then statement
    By crispy in forum ASP
    Replies: 6
    Last Post: July 9th, 07:27 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139