Table 1 : airport
Id airportname
1 NYK(newyork)
2 DXB (dubai)
3 BOM (Bombay)
4 DEL (delhi)
Now My Rote details
First Last Detail Route
1 : Nyk - BOM : Nyk - DXB - BOM
2 : Nyk - Del : Nyk - DXB - BOM -Del
Table 2 : segmentdetails
SegmentdetailsId RouteId Originairport destibation airport
1 1 1 2
2 1 2 3
3 1 3 4
4 2 1 2
5 2 2 3
Note: user can journy any segment wise on route e.g. Nyk to DXB (1-2),Nyk - Bom(1 - 3) ,Nyk- Del (1- 4) or Dxb - Bom(2 - 4) etc..
Now my question is when I pass in parameter as origin airport ID : 1 and destination ID : 4 so my result would be come as below table in store procedure
CASE - 1 Temptable: routewise_segmentdetails
SegmentdetailsId RouteId
1 1
2 1
3 1
OR
Case - 2 I pass in parameter as origin airport ID : 1 and destination ID : 3 so my result would be come as below table
Temptable: routewise_segmentdetails
SegmentdetailsId RouteId
1 1
2 1
4 2
5 2
OR
Case - 3 I pass in parameter as origin airport ID : 2 and destination ID : 3 so my result would be come as below table
Temptable: routewise_segmentdetails
SegmentdetailsId RouteId
2 1
5 2
might be I can use nested cursor then I get values but I have not idea about nested cursor with table.
Seeing as the structure of your table - and the way you store data means that segmentDetails are stored sequentially. Given that bit of info, you don't need to store any stuffs in temp tables or anything like that at all.
I think that this should do the trick - you just write two subqueries, each pulling a distinct routeId and joining it back to the original table as well as each other, but also constrain the outer query by the same params you use in the subqueries:
select
core.segmentDetailsId,
core.routeId
from
segmentDetails core
join (
select distinct
routeId
from
segmentDetails
where
originAirport=param1
) sub1
on core.routeId=sub1.routeId
join (
select distinct
routeId
from
segmentDetails
where
destinationAirport=param2
) sub2
on core.routeId=sub2.routeId
and sub1.routeId=sub2.routeId
where
core.originAirport>=param1
and core.destinationAirport<=param2
order by
segmentDetailsId,
routeId
Edit: I haven't actually tested this, but I think it is likely to work. If not, leave me a comment and I will have another look.
Edit 2: I think your case 2 is wrong based on what you seem to be asking:
Case ------- 2 I pass in parameter as origin airport ID : 1 and destination ID : 3 so my result would be come as below table
Temptable: routewise_segmentdetails
SegmentdetailsId RouteId
1 1
2 1
3?? 1??
4 2
5 2
You put destination as 3, yet you are showing SegDetId 3, which has an ORIGIN of 3, yet a destination of 4. I take it you meant to NOT show this row in your results?