Join on a view T-SQL

Question

I'm making a view. What i now have is:

CREATE VIEW [dbo].[vwEventDetails]
AS
SELECT 
    ISNULL(ROW_NUMBER() OVER (ORDER BY EventID), 999999999) AS Row,
    STUFF(DetectorID, len(DetectorID), 1, '0') as SiteID,
    DetectorID AS DetectorID,
    StartedOn AS StartedOn,
    EndedOn AS EndedON,
    EventDescription AS EventDescription,
    EventCategoryID AS EventCategoryID,
    EventSeverityLevelID AS EventSeverityLevelID,
    EventStatusID AS EventStatusID,
    Processed AS Processed,
    CASE WHEN EndedOn IS NOT NULL
        THEN
            DATEDIFF(SECOND, StartedOn, EndedOn)/ 3600.0
        ELSE 
            DATEDIFF(SECOND, StartedOn, CURRENT_TIMESTAMP)/ 3600.0
    END
        AS Duration

FROM Event

GO

The result that i'm getting from this view is:

The result i'm getting is correct. However, i need 1 more value which is not in the Event table. And i dont know how to get this value.

The view is based on the Event table. which looks like this:

Now in this table there is a row called DetectorID.
DetectorID leads to the table: Detector:

In this table, you'll see a row named TrackID.
TrackID leads to the table Track:

In the table there is a row named TrackName.
This is the value that i want in the view. Is there a way to achieve this?

So basically a short summary. Is there a way to go from:

Event -> Detector -> Track

via a view which is based on Event?

Answer 1

Joining to other tables is a fairly standard thing in sql.

You can use an inner join to only return rows where there is a matching Detector and Track, or you could use left join when there may not be matching values in the corresponding tables.

Also, row_number() will not return null, no reason to wrap that in isnull().

--create view dbo.vwEventDetails as
select 
    row_number() over (order by e.EventId) as Row
  , stuff(DetectorId, len(e.DetectorId), 1, '0') as SiteId
  , e.DetectorId
  , e.StartedO
  , e.EndedOn
  , e.EventDescription
  , e.EventCategoryId
  , e.EventSeverityLevelId
  , e.EventStatusId
  , e.Processed
  , case when e.EndedOn is not null
        then datediff(second, e.StartedOn, e.EndedOn)/ 3600.0
      else datediff(second, e.StartedOn, current_timestamp)/ 3600.0
      end as Duration
  , t.TrackId
  , t.FromName
  , t.ToName
  , t.TrackName
from Event e
  inner join Detector d
    on e.DetectorId = d.DetectorId
  inner join Track t
    on d.TrackId = t.TrackId