SQL & PL/SQL

Hi Gurus,

I want to find all paths from source node to target node that shall be sorted by the cost. I don't know whether it could be achieved with SQL statement. How to start with this query? The script to create the underlying tables along with the data are given below:

------------------------------------------------------------------------------------------------------------------
create table nodes ( nodeId int Primary Key, nodeName varchar(50));

create table paths ( pathId int Primary Key, fromNodeId int, toNodeId int, cost int );

insert into nodes values (1,'ISL');
insert into nodes values (2,'LHR');
insert into nodes values (3,'HYD');
insert into nodes values (4,'FSL');
insert into nodes values (5,'MUL');
insert into nodes values (6,'KHI');
insert into nodes values (7,'QT');

insert into paths values (1,1,3,20);
insert into paths values (2,1,5,10);
insert into paths values (3,1,7,80);
insert into paths values (4,2,4,10);
insert into paths values (5,3,4,40);
insert into paths values (6,3,5,20);
insert into paths values (7,3,6,10);
insert into paths values (8,6,7,30);
insert into paths values (9,6,5,30);
insert into paths values (10,6,3,10);
insert into paths values (11,7,2,20);
insert into paths values (12,5,4,40);
insert into paths values (13,5,7,40);

----------------------------------------------------------------------------------------------------------------------------------
Suppose the source = ISL and target = QT, their are various paths from ISL to QT:

Ord# Relative Path Cost
==== ================================= =================
1. ISL -> MUL -> QT 50
2. ISL -> HYD -> KHI -> QT 60
3. ISL -> QT 80
4. ISL -> HYD -> MUL -> QT 80
5. ISL -> HYD to KHI -> MUL -> QT 100

This gives us all possible paths sorted by cost.

Any hint or help will be highly appreciated.

Thanks in advance and best regards

Bilal

Edited by: naive2Oracle on Feb 11, 2011 9:59 AM