1

Hi I have created a query to find employee details and supervisor details of an organization now i want that every employees name should be repeated in the supervisor column also once. Means :

  employee num Supervisor num 

     1            2 
   **1            1**
     2            3
   **2            2** 
     3            4
   etc

The query i wrote to get employee number and supervisor num is :-

 Select a.employee_num,a.supervisor_num
 from managers a;

This query will just give me....

 employee num Supervisor num 

     1            2 
     2            3
     3            4

Any suggestion will be helpful :)

user3809240
  • 93
  • 1
  • 3
  • 18

1 Answers1

2

You could try this:

(SELECT a.employee_num,
        a.supervisor_num
 FROM managers a)
UNION ALL
(SELECT DISTINCT a.employee_num,
        a.employee_num AS supervisor_num
 FROM managers a)
ORDER BY 1,2

The first query is just like the one you created. The second will add every employee as a manager. Ordering the whole union will create the resultset you want.

Mariano D'Ascanio
  • 1,202
  • 2
  • 16
  • 17
  • +1 for exposing me to order by field index. http://stackoverflow.com/questions/354224/combining-union-all-and-order-by-in-firebird – srage Jul 10 '14 at 05:04