CODEWITHSUNDEEP
sqlsql-server
Chen_Dogg
Chen_Dogg

Reputation: 91

Include an extra column from an original table that has an Inner Join that is listing values that only occur in a lookup table

I have 2 tables, the first includes a data feed (Data Import) feeding in checkpoints that are clocked on various 'patrols'. These patrols are linked to 'routes' (multiple patrols around the same routes). I have the below SQL that ensures that a view is created that results in only the missed checkpoints are listed for each patrol conducted. This is working fine, but I need to include the time from the original table (data import table) that the checkpoint was clocked. How would I go about ensuring that this time is included in the output view? The field in the original table to be included is Occurrence Date, and resides in the Data Import Table

Here is my current SQL (working):

CREATE VIEW MissedPoints AS
SELECT Routes.[Route id]
     ,[Data Import].[Patrol id]
     ,Routes.[Tag Name]
FROM Routes 
INNER JOIN (SELECT DISTINCT [Route Id]
                ,[Patrol ID] 
            from [Data Import]) as [Data Import] 
    on [Data Import].[Route Id] = Routes.[Route Id]
EXCEPT
SELECT [Route Id]
    ,[Patrol ID]
    ,[Tag name]
FROM [Data Import]

I just cant seem to get the right SQL in the right place.

Upvotes: 0

Views: 98

Answers (1)

Daniel E.
Daniel E.

Reputation: 2059

My guess is that you actually want the following. This will give you only the missed checkpoints, assuming that the missed times would be NULL if not you can change the where criteria to whatever your missed value is, or put the two lines I marked added into your query in the same place, as well as 1 line in the except query to make the fields match up again (But lose the where clause).

CREATE VIEW MissedPoints AS
SELECT Routes.[Route id]
     ,[Data Import].[Patrol id]
     ,Routes.[Tag Name]
     ,[Data Import].[Occurrence Date]  --Added
FROM Routes 
LEFT JOIN (SELECT DISTINCT [Route Id]
                ,[Patrol ID] 
                ,[Occurrence Date]  --Added
            from [Data Import]) as [Data Import] 
    on [Data Import].[Route Id] = Routes.[Route Id]
WHERE [Data Import].[Occurrence Date] is NULL

Upvotes: 1

Related Questions