Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Joining two tables with sort
I have some problems extracting sorted data from two tables. The tables looks somthing like this:
department: refnr INT PRIMARY KEY, location VARCHAR(2000)
employee:
refnr INT REFERENCES department(refnr), seq INT KEY, name VARCHAR(2000)
I want to extract a list of all departments, and all their employees. The departments should be ordered by "seq, name" for the employees, and the employees within a given department should be ordered by seq (thus, if two departments have a person named Doe with seq=1, we need to look at the person with seq=2 to determine which department to print first).
Apparently the correct solution is not to join the two tables with a "SELECT ... FROM department d, emplyee e", as this seems to take an awfull lot of time (the tables are huge), and location would be returned multiple times. If required, modification of the database model is an alternative.
[Using Oraperl and Oracle 7.2.2.3.0]
-- Rune Frøysa, IT-Seksjonen UB, UiO. Private e-mail: runefr_at_ifi.uio.no WWW: http://www.ifi.uio.no/%7Erunefr/ Work e-mail: rune.froysa@ub.uio.no Amnesty: http://www.amnesty.no Pb 1059 Blindern, 0316 OsloReceived on Wed Oct 22 1997 - 00:00:00 CDT