How to get data order by Sort Value

Question

I have three tables called Service , Location and SeriveLocation.

Service Table data as follows

ServiceID       Service     SortValue
1           Customer call       5
2               Reload          2
3           Internet setting    3
4               E care          7
5               Anti call       4
6               MMS             1
7            settings           6

And SeriveLocation Table data as follows

FkLcoationID    FkServiceID   SortValue
001                 1           2
002                 1           1
003                 1           2
004                 1           NULL
005                 1           4
001                 2           3
002                 2           4
003                 2           4
004                 2           NULL
005                 2           1
001                 3           4
002                 3           2
003                 3           3
004                 3           NULL
005                 3           3
001                 4           1
002                 4           3
003                 4           1
004                 4           NULL
005                 4           2

Now I need to get particular location data with Its services and serviceIDs order by SeriveLocation table's sortValue. If SeriveLocation's table sortValue is NULL I need to get an order of services using Service table's SortValue

My expected output is when locationId = 001

When LocationId = 004 (according mapping table, there is no sortVlaue for location 004, therefor its sort order need to take from service table)

How can I do this?

I tried this for, when Sortvalue is Null

select sm.FkBranchId,sm.FkServiceId,s.ServiceName from SeriveLocation sm
INNER JOIN Service s ON s.ServiceID = sm.FkServiceId
where sm.FkLocationId = 004 
order by s.SortValue 

Answer 1

I think you want a left join and coalesce():

select s.Service, sl.ServiceId,
       coalesce(sl.SortValue, s.SortValue)
from ServiceLocation sl left join
     Service s
     on sl.ServiceId = s.ServiceId
where sl.FkLocationId  = ?
order by coalesce(sl.SortValue, s.SortValue);