2

I have the following SQL query:

select *
from NETWORK n
inner join vwNETWORK_KEYMSTN km on n.Network_ID = km.Network_ID
inner join vwAPPROVAL_LATEST a on n.Network_ID = a.Network_ID
inner join APPROVAL_VINTAGE av on a.Approval_ID = av.Approval_ID and km.Milestone_Type_ID = av.Milestone_Type_ID
inner join NETWORK_MILESTONE m on A.Approval_ID = m.Approval_ID and km.Milestone_Type_ID = m.Milestone_Type_ID
inner join REF_MILESTONE rm on km.Milestone_Type_ID = rm.Milestone_Type_ID
inner join NETWORK_STATUS s on n.Network_ID = s.Network_ID --and s.Status_Type_ID = 2
--where rm.Status_Type_ID = s.Status_Type_ID 

This query returns in about 6s. The last table, NETWORK_STATUS, causes my result set to split out. That is, n.Network_ID would be unique before adding that last table NETWORK_STATUS to the join.

Adding the join clause that is currently commented out will return a unique set of Network_ID again. The commented out join will a result set in fast time, ie, < 10s.

However, what I truly want is the where clause that is commented out, or I could put it as part of the join. A MILESTONE_type is associated with only one STATUS_type. Putting this part in, which is seemingly a trivial addition to the query, causes a massive explosion in run time... I kill the query after a minute, but it could be even greater than a 6x factor increase in run time.

My question to this forum is why that might be the case, and more importantly, how do I tell SQL Server that this really isn't a hard where clause to execute. At most, the where clause should return in < 20s... it could return the full set with duplicates (~6s), and then 2nd pass simply trim out the rows that don't satisfy the where clause.

Execution Plan: here is the problem line:

--Index Scan(OBJECT:([CSDConstruction].[dbo].[NETWORK_STATUS].[IX_NETWORK_STATUS_Status_Type_ID] AS [s]),  WHERE:(PROBE([Opt_Bitmap1070],[CSDConstruction].[dbo].[NETWORK_STATUS].[Status_Type_ID] as [s].[Status_Type_ID],N'[IN ROW]')))

Here is the full execution plan:

  |--Compute Scalar(DEFINE:([Expr1029]=CASE WHEN [CSDConstruction].[dbo].[NETWORK_MILESTONE].[Milestone_DT] as [m1].[Milestone_DT]<>(-1) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [cprd].[Milestone_Type_ID] ELSE CASE WHEN isnull([Expr1015],($0.0000))>=isnull([Expr1016],($0.0000)) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [fcom].[Milestone_Type_ID] ELSE CASE WHEN [CSDConstruction].[dbo].[NETWORK].[Network_NM] as [n].[Network_NM] like '%:EQ%' OR [CSDConstruction].[dbo].[NETWORK].[Network_NM] as [n].[Network_NM] like '%:IP' THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [fcom].[Milestone_Type_ID] ELSE CASE WHEN isnull([Expr1015],($0.0000))<isnull([Expr1016],($0.0000)) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [nisr].[Milestone_Type_ID] ELSE [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [unknown].[Milestone_Type_ID] END END END END))
       |--Parallelism(Gather Streams)
            |--Nested Loops(Inner Join, WHERE:(CASE WHEN [CSDConstruction].[dbo].[NETWORK_MILESTONE].[Milestone_DT] as [m1].[Milestone_DT]<>(-1) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [cprd].[Milestone_Type_ID] ELSE CASE WHEN isnull([Expr1015],($0.0000))>=isnull([Expr1016],($0.0000)) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [fcom].[Milestone_Type_ID] ELSE CASE WHEN [CSDConstruction].[dbo].[NETWORK].[Network_NM] as [n].[Network_NM] like '%:EQ%' OR [CSDConstruction].[dbo].[NETWORK].[Network_NM] as [n].[Network_NM] like '%:IP' THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [fcom].[Milestone_Type_ID] ELSE CASE WHEN isnull([Expr1015],($0.0000))<isnull([Expr1016],($0.0000)) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [nisr].[Milestone_Type_ID] ELSE [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [unknown].[Milestone_Type_ID] END END END END=[CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [rm].[Milestone_Type_ID] AND CASE WHEN [CSDConstruction].[dbo].[NETWORK_MILESTONE].[Milestone_DT] as [m1].[Milestone_DT]<>(-1) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [cprd].[Milestone_Type_ID] ELSE CASE WHEN isnull([Expr1015],($0.0000))>=isnull([Expr1016],($0.0000)) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [fcom].[Milestone_Type_ID] ELSE CASE WHEN [CSDConstruction].[dbo].[NETWORK].[Network_NM] as [n].[Network_NM] like '%:EQ%' OR [CSDConstruction].[dbo].[NETWORK].[Network_NM] as [n].[Network_NM] like '%:IP' THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [fcom].[Milestone_Type_ID] ELSE CASE WHEN isnull([Expr1015],($0.0000))<isnull([Expr1016],($0.0000)) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [nisr].[Milestone_Type_ID] ELSE [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [unknown].[Milestone_Type_ID] END END END END=[CSDConstruction].[dbo].[NETWORK_MILESTONE].[Milestone_Type_ID] as [m].[Milestone_Type_ID] AND CASE WHEN [CSDConstruction].[dbo].[NETWORK_MILESTONE].[Milestone_DT] as [m1].[Milestone_DT]<>(-1) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [cprd].[Milestone_Type_ID] ELSE CASE WHEN isnull([Expr1015],($0.0000))>=isnull([Expr1016],($0.0000)) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [fcom].[Milestone_Type_ID] ELSE CASE WHEN [CSDConstruction].[dbo].[NETWORK].[Network_NM] as [n].[Network_NM] like '%:EQ%' OR [CSDConstruction].[dbo].[NETWORK].[Network_NM] as [n].[Network_NM] like '%:IP' THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [fcom].[Milestone_Type_ID] ELSE CASE WHEN isnull([Expr1015],($0.0000))<isnull([Expr1016],($0.0000)) THEN [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [nisr].[Milestone_Type_ID] ELSE [CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_Type_ID] as [unknown].[Milestone_Type_ID] END END END END=[CSDConstruction].[dbo].[APPROVAL_VINTAGE].[Milestone_Type_ID] as [av].[Milestone_Type_ID]))
                 |--Nested Loops(Inner Join)
                 |    |--Nested Loops(Inner Join)
                 |    |    |--Hash Match(Inner Join, HASH:([cprd].[Milestone_Type_ID])=([m1].[Milestone_Type_ID]))
                 |    |    |    |--Parallelism(Distribute Streams, Broadcast Partitioning)
                 |    |    |    |    |--Clustered Index Scan(OBJECT:([CSDConstruction].[dbo].[REF_MILESTONE].[PK_REF_NETWORK_MILESTONE] AS [cprd]), WHERE:([CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_NM] as [cprd].[Milestone_NM]='CPRD'))
                 |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([m1].[Milestone_ID], [Expr1078]) WITH UNORDERED PREFETCH)
                 |    |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[Approval_ID], [Expr1077]) WITH UNORDERED PREFETCH)
                 |    |    |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([m].[Milestone_ID], [Expr1076]) WITH ORDERED PREFETCH)
                 |    |    |         |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[Approval_ID]))
                 |    |    |         |    |    |    |--Sort(ORDER BY:([b].[Approval_ID] ASC))
                 |    |    |         |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1008], [ra].[Approval_Type_ID], [s].[Network_ID]))
                 |    |    |         |    |    |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[Approval_ID]))
                 |    |    |         |    |    |    |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[Approval_Type_ID], [Expr1034], [s].[Network_ID]))
                 |    |    |         |    |    |    |         |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[Network_ID]))
                 |    |    |         |    |    |    |         |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[Network_ID]))
                 |    |    |         |    |    |    |         |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[Approval_Type_ID]))
                 |    |    |         |    |    |    |         |    |    |    |    |    |--Stream Aggregate(GROUP BY:([s].[Network_Status_ID], [rm].[Milestone_Type_ID], [a].[Approval_Type_ID], [a].[Approval_Type_ID]) DEFINE:([Expr1008]=MAX([CSDConstruction].[dbo].[NETWORK_APPROVAL].[Approval_Version_NUM] as [a].[Approval_Version_NUM]), [Expr1015]=ANY([Expr1015]), [Expr1016]=ANY([Expr1016]), [Expr1034]=ANY([Expr1034]), [rm].[Milestone_NM]=ANY([CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_NM] as [rm].[Milestone_NM]), [rm].[Status_Type_ID]=ANY([CSDConstruction].[dbo].[REF_MILESTONE].[Status_Type_ID] as [rm].[Status_Type_ID]), [s].[Network_ID]=ANY([CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID]), [s].[Status_Type_ID]=ANY([CSDConstruction].[dbo].[REF_MILESTONE].[Status_Type_ID] as [rm].[Status_Type_ID]), [s].[Status_DT]=ANY([CSDConstruction].[dbo].[NETWORK_STATUS].[Status_DT] as [s].[Status_DT]), [s].[Current_IND]=ANY([CSDConstruction].[dbo].[NETWORK_STATUS].[Current_IND] as [s].[Current_IND])))
                 |    |    |         |    |    |    |         |    |    |    |    |    |    |--Sort(ORDER BY:([s].[Network_Status_ID] ASC, [rm].[Milestone_Type_ID] ASC, [a].[Approval_Type_ID] ASC, [a].[Approval_Type_ID] ASC))
                 |    |    |         |    |    |    |         |    |    |    |    |    |         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([s].[Network_Status_ID], [rm].[Milestone_Type_ID], [a].[Approval_Type_ID], [a].[Approval_Type_ID]))
                 |    |    |         |    |    |    |         |    |    |    |    |    |              |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[Network_ID]))
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |--Stream Aggregate(GROUP BY:([s].[Network_Status_ID], [rm].[Milestone_Type_ID], [a].[Approval_Type_ID]) DEFINE:([Expr1034]=MAX([CSDConstruction].[dbo].[NETWORK_APPROVAL].[Approval_Version_NUM] as [a].[Approval_Version_NUM]), [Expr1015]=ANY([Expr1015]), [Expr1016]=ANY([Expr1016]), [rm].[Milestone_NM]=ANY([CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_NM] as [rm].[Milestone_NM]), [rm].[Status_Type_ID]=ANY([CSDConstruction].[dbo].[REF_MILESTONE].[Status_Type_ID] as [rm].[Status_Type_ID]), [s].[Network_ID]=ANY([CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID]), [s].[Status_Type_ID]=ANY([CSDConstruction].[dbo].[REF_MILESTONE].[Status_Type_ID] as [rm].[Status_Type_ID]), [s].[Status_DT]=ANY([CSDConstruction].[dbo].[NETWORK_STATUS].[Status_DT] as [s].[Status_DT]), [s].[Current_IND]=ANY([CSDConstruction].[dbo].[NETWORK_STATUS].[Current_IND] as [s].[Current_IND])))
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |    |--Sort(ORDER BY:([s].[Network_Status_ID] ASC, [rm].[Milestone_Type_ID] ASC, [a].[Approval_Type_ID] ASC))
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([s].[Network_Status_ID], [rm].[Milestone_Type_ID], [a].[Approval_Type_ID]))
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |              |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[Network_ID]))
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |                   |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[Network_ID]))
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |                   |    |--Hash Match(Inner Join, HASH:([rm].[Status_Type_ID])=([s].[Status_Type_ID]))
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |                   |    |    |--Bitmap(HASH:([rm].[Status_Type_ID]), DEFINE:([Opt_Bitmap1070]))
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |                   |    |    |    |--Parallelism(Distribute Streams, Broadcast Partitioning)
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |                   |    |    |         |--Clustered Index Scan(OBJECT:([CSDConstruction].[dbo].[REF_MILESTONE].[PK_REF_NETWORK_MILESTONE] AS [rm]))
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |                   |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[Network_Status_ID]))
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |                   |    |         |--Parallelism(Repartition Streams, RoundRobin Partitioning)
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |                   |    |         |    |--Index Scan(OBJECT:([CSDConstruction].[dbo].[NETWORK_STATUS].[IX_NETWORK_STATUS_Status_Type_ID] AS [s]),  WHERE:(PROBE([Opt_Bitmap1070],[CSDConstruction].[dbo].[NETWORK_STATUS].[Status_Type_ID] as [s].[Status_Type_ID],N'[IN ROW]')))
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |                   |    |         |--Clustered Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_STATUS].[PK_NETWORK_STATUS] AS [s]), SEEK:([s].[Network_Status_ID]=[CSDConstruction].[dbo].[NETWORK_STATUS].[Network_Status_ID] as [s].[Network_Status_ID]) LOOKUP ORDERED FORWARD)
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |                   |    |--Compute Scalar(DEFINE:([Expr1015]=CASE WHEN [Expr1072]=(0) THEN NULL ELSE [Expr1073] END, [Expr1016]=CASE WHEN [Expr1074]=(0) THEN NULL ELSE [Expr1075] END))
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |                   |         |--Stream Aggregate(DEFINE:([Expr1072]=COUNT_BIG(CASE WHEN [CSDConstruction].[dbo].[REF_CEGROUP].[CEGroup_NM] as [ret].[CEGroup_NM] like '3_IP%' OR [CSDConstruction].[dbo].[REF_CEGROUP].[CEGroup_NM] as [ret].[CEGroup_NM] like '3_Ot%' THEN [CSDConstruction].[dbo].[NETWORK_EXPENSE].[Expense_AMT] as [e].[Expense_AMT] ELSE NULL END), [Expr1073]=SUM(CASE WHEN [CSDConstruction].[dbo].[REF_CEGROUP].[CEGroup_NM] as [ret].[CEGroup_NM] like '3_IP%' OR [CSDConstruction].[dbo].[REF_CEGROUP].[CEGroup_NM] as [ret].[CEGroup_NM] like '3_Ot%' THEN [CSDConstruction].[dbo].[NETWORK_EXPENSE].[Expense_AMT] as [e].[Expense_AMT] ELSE NULL END), [Expr1074]=COUNT_BIG(CASE WHEN [CSDConstruction].[dbo].[REF_CEGROUP].[CEGroup_NM] as [ret].[CEGroup_NM] like '3_OP%' THEN [CSDConstruction].[dbo].[NETWORK_EXPENSE].[Expense_AMT] as [e].[Expense_AMT] ELSE NULL END), [Expr1075]=SUM(CASE WHEN [CSDConstruction].[dbo].[REF_CEGROUP].[CEGroup_NM] as [ret].[CEGroup_NM] like '3_OP%' THEN [CSDConstruction].[dbo].[NETWORK_EXPENSE].[Expense_AMT] as [e].[Expense_AMT] ELSE NULL END)))
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |                   |              |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[CEGroup_Type_ID]))
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |                   |                   |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[Network_Expense_ID]))
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |                   |                   |    |--Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_EXPENSE].[IX_NETWORK_EXPENSE_NETWORK_ID] AS [e]), SEEK:([e].[Network_ID]=[CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID]) ORDERED FORWARD)
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |                   |                   |    |--Clustered Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_EXPENSE].[PK_NetworkSpend] AS [e]), SEEK:([e].[Network_Expense_ID]=[CSDConstruction].[dbo].[NETWORK_EXPENSE].[Network_Expense_ID] as [e].[Network_Expense_ID]) LOOKUP ORDERED FORWARD)
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |                   |                   |--Clustered Index Seek(OBJECT:([CSDConstruction].[dbo].[REF_CEGROUP].[PK_REF_CEGrp] AS [ret]), SEEK:([ret].[CEGroup_Type_ID]=[CSDConstruction].[dbo].[NETWORK_EXPENSE].[CEGroup_Type_ID] as [e].[CEGroup_Type_ID]),  WHERE:([CSDConstruction].[dbo].[REF_CEGROUP].[CEGroup_NM] as [ret].[CEGroup_NM] like '3%') ORDERED FORWARD)
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |                   |--Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_APPROVAL].[IX_NETWORK_APPROVAL] AS [a]), SEEK:([a].[Network_ID]=[CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID]) ORDERED FORWARD)
                 |    |    |         |    |    |    |         |    |    |    |    |    |                   |--Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_APPROVAL].[IX_NETWORK_APPROVAL] AS [a]), SEEK:([a].[Network_ID]=[CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID]) ORDERED FORWARD)
                 |    |    |         |    |    |    |         |    |    |    |    |    |--Clustered Index Seek(OBJECT:([CSDConstruction].[dbo].[REF_NTWK_APPROVAL_STATUS].[PK_REF_MILESTONE_VERSION] AS [ra]), SEEK:([ra].[Approval_Type_ID]=[CSDConstruction].[dbo].[NETWORK_APPROVAL].[Approval_Type_ID] as [a].[Approval_Type_ID]),  WHERE:([CSDConstruction].[dbo].[REF_NTWK_APPROVAL_STATUS].[Approval_NM] as [ra].[Approval_NM]='LNA') ORDERED FORWARD)
                 |    |    |         |    |    |    |         |    |    |    |    |--Clustered Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK].[PK_DimNetwork] AS [n]), SEEK:([n].[Network_ID]=[CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID]) ORDERED FORWARD)
                 |    |    |         |    |    |    |         |    |    |    |--Clustered Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK].[PK_DimNetwork] AS [n]), SEEK:([n].[Network_ID]=[CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID]) ORDERED FORWARD)
                 |    |    |         |    |    |    |         |    |    |--Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_APPROVAL].[IX_NETWORK_APPROVAL] AS [b]), SEEK:([b].[Network_ID]=[CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID] AND [b].[Approval_Type_ID]=[CSDConstruction].[dbo].[NETWORK_APPROVAL].[Approval_Type_ID] as [a].[Approval_Type_ID] AND [b].[Approval_Version_NUM]=[Expr1034]) ORDERED FORWARD)
                 |    |    |         |    |    |    |         |    |--Index Seek(OBJECT:([CSDConstruction].[dbo].[APPROVAL_VINTAGE].[IX_APPROVAL_VINTAGE] AS [av]), SEEK:([av].[Approval_ID]=[CSDConstruction].[dbo].[NETWORK_APPROVAL].[Approval_ID] as [b].[Approval_ID]) ORDERED FORWARD)
                 |    |    |         |    |    |    |         |--Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_APPROVAL].[IX_NETWORK_APPROVAL] AS [b]), SEEK:([b].[Network_ID]=[CSDConstruction].[dbo].[NETWORK_STATUS].[Network_ID] as [s].[Network_ID] AND [b].[Approval_Type_ID]=[CSDConstruction].[dbo].[REF_NTWK_APPROVAL_STATUS].[Approval_Type_ID] as [ra].[Approval_Type_ID] AND [b].[Approval_Version_NUM]=[Expr1008]) ORDERED FORWARD)
                 |    |    |         |    |    |    |--Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_MILESTONE].[IX_NETWORK_MILESTONE] AS [m]), SEEK:([m].[Approval_ID]=[CSDConstruction].[dbo].[NETWORK_APPROVAL].[Approval_ID] as [b].[Approval_ID]) ORDERED FORWARD)
                 |    |    |         |    |    |--Clustered Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_MILESTONE].[PK_MILESTONE] AS [m]), SEEK:([m].[Milestone_ID]=[CSDConstruction].[dbo].[NETWORK_MILESTONE].[Milestone_ID] as [m].[Milestone_ID]) LOOKUP ORDERED FORWARD)
                 |    |    |         |    |--Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_MILESTONE].[IX_NETWORK_MILESTONE] AS [m1]), SEEK:([m1].[Approval_ID]=[CSDConstruction].[dbo].[NETWORK_APPROVAL].[Approval_ID] as [b].[Approval_ID]) ORDERED FORWARD)
                 |    |    |         |--Clustered Index Seek(OBJECT:([CSDConstruction].[dbo].[NETWORK_MILESTONE].[PK_MILESTONE] AS [m1]), SEEK:([m1].[Milestone_ID]=[CSDConstruction].[dbo].[NETWORK_MILESTONE].[Milestone_ID] as [m1].[Milestone_ID]) LOOKUP ORDERED FORWARD)
                 |    |    |--Clustered Index Scan(OBJECT:([CSDConstruction].[dbo].[REF_MILESTONE].[PK_REF_NETWORK_MILESTONE] AS [nisr]), WHERE:([CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_NM] as [nisr].[Milestone_NM]='NISR'))
                 |    |--Clustered Index Scan(OBJECT:([CSDConstruction].[dbo].[REF_MILESTONE].[PK_REF_NETWORK_MILESTONE] AS [unknown]), WHERE:([CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_NM] as [unknown].[Milestone_NM]='UNKNOWN'))
                 |--Clustered Index Scan(OBJECT:([CSDConstruction].[dbo].[REF_MILESTONE].[PK_REF_NETWORK_MILESTONE] AS [fcom]), WHERE:([CSDConstruction].[dbo].[REF_MILESTONE].[Milestone_NM] as [fcom].[Milestone_NM]='FCOM'))

Here is what the data looks like:

Network_ID  Network_NUM Network_NM  Network_ID  Milestone_Type_ID   Approval_ID Network_ID  Approval_Type_ID    Approval_Version_NUM    Approval_Vintage_ID Approval_ID Milestone_Type_ID   Milestone_ID    Approval_ID Milestone_Type_ID   Milestone_DT    Milestone_Type_ID   Milestone_NM    Status_Type_ID  Network_Status_ID   Network_ID  Status_Type_ID  Status_DT   Current_IND
613693  2027581 GCC:CYPS:FEEDER FROM NEW GLENAYRE HUB   613693  6   716823  613693  1   2   85707   716823  6   2493490 716823  6   20080229    6   NISR    6   352472  613693  1   20010307    0
613693  2027581 GCC:CYPS:FEEDER FROM NEW GLENAYRE HUB   613693  6   716823  613693  1   2   85707   716823  6   2493490 716823  6   20080229    6   NISR    6   633324  613693  2   20010614    0
613693  2027581 GCC:CYPS:FEEDER FROM NEW GLENAYRE HUB   613693  6   716823  613693  1   2   85707   716823  6   2493490 716823  6   20080229    6   NISR    6   948846  613693  3   20030924    0
613693  2027581 GCC:CYPS:FEEDER FROM NEW GLENAYRE HUB   613693  6   716823  613693  1   2   85707   716823  6   2493490 716823  6   20080229    6   NISR    6   1264368 613693  4   20031007    0
613693  2027581 GCC:CYPS:FEEDER FROM NEW GLENAYRE HUB   613693  6   716823  613693  1   2   85707   716823  6   2493490 716823  6   20080229    6   NISR    6   1579890 613693  5   20090515    0
613693  2027581 GCC:CYPS:FEEDER FROM NEW GLENAYRE HUB   613693  6   716823  613693  1   2   85707   716823  6   2493490 716823  6   20080229    6   NISR    6   1895412 613693  6   20090515    0
613693  2027581 GCC:CYPS:FEEDER FROM NEW GLENAYRE HUB   613693  6   716823  613693  1   2   85707   716823  6   2493490 716823  6   20080229    6   NISR    6   2210934 613693  7   20100526    0
613693  2027581 GCC:CYPS:FEEDER FROM NEW GLENAYRE HUB   613693  6   716823  613693  1   2   85707   716823  6   2493490 716823  6   20080229    6   NISR    6   2526456 613693  8   20100901    0
618637  2114067 BJK:RCMD:8120 LANSDOWNE RD 256 UNIT HIGH    618637  6   720878  618637  1   2   92712   720878  6   2497545 720878  6   20090327    6   NISR    6   457111  618637  1   20031020    0
618637  2114067 BJK:RCMD:8120 LANSDOWNE RD 256 UNIT HIGH    618637  6   720878  618637  1   2   92712   720878  6   2497545 720878  6   20090327    6   NISR    6   637149  618637  2   20031027    0
618637  2114067 BJK:RCMD:8120 LANSDOWNE RD 256 UNIT HIGH    618637  6   720878  618637  1   2   92712   720878  6   2497545 720878  6   20090327    6   NISR    6   952671  618637  3   20081210    0
618637  2114067 BJK:RCMD:8120 LANSDOWNE RD 256 UNIT HIGH    618637  6   720878  618637  1   2   92712   720878  6   2497545 720878  6   20090327    6   NISR    6   1268193 618637  4   20090811    0
618637  2114067 BJK:RCMD:8120 LANSDOWNE RD 256 UNIT HIGH    618637  6   720878  618637  1   2   92712   720878  6   2497545 720878  6   20090327    6   NISR    6   1583715 618637  5   20090911    0
618637  2114067 BJK:RCMD:8120 LANSDOWNE RD 256 UNIT HIGH    618637  6   720878  618637  1   2   92712   720878  6   2497545 720878  6   20090327    6   NISR    6   1899237 618637  6   20090911    0
618637  2114067 BJK:RCMD:8120 LANSDOWNE RD 256 UNIT HIGH    618637  6   720878  618637  1   2   92712   720878  6   2497545 720878  6   20090327    6   NISR    6   2214759 618637  7   20100305    0
618637  2114067 BJK:RCMD:8120 LANSDOWNE RD 256 UNIT HIGH    618637  6   720878  618637  1   2   92712   720878  6   2497545 720878  6   20090327    6   NISR    6   2530281 618637  8   20100701    0
621017  2135192 RAD:NWTN:8 LOT SUBD EDINBURGH DR N/O 88 621017  6   555884  621017  1   3   71742   555884  6   2332551 555884  6   20091127    6   NISR    6   542606  621017  1   20040525    0
621017  2135192 RAD:NWTN:8 LOT SUBD EDINBURGH DR N/O 88 621017  6   555884  621017  1   3   71742   555884  6   2332551 555884  6   20091127    6   NISR    6   639371  621017  2   20040525    0
621017  2135192 RAD:NWTN:8 LOT SUBD EDINBURGH DR N/O 88 621017  6   555884  621017  1   3   71742   555884  6   2332551 555884  6   20091127    6   NISR    6   954893  621017  3   20091020    0
621017  2135192 RAD:NWTN:8 LOT SUBD EDINBURGH DR N/O 88 621017  6   555884  621017  1   3   71742   555884  6   2332551 555884  6   20091127    6   NISR    6   1270415 621017  4   20091020    0

Trying to select the subset where the 3rd and 6th columns from the end match.

4

If you don't need the other data from the NETWORK_STATUS table, how about:

select *
from dbo.NETWORK AS n
inner join dbo.vwNETWORK_KEYMSTN AS km 
  on n.Network_ID = km.Network_ID
inner join dbo.vwAPPROVAL_LATEST AS a 
  on n.Network_ID = a.Network_ID
inner join dbo.APPROVAL_VINTAGE  AS av 
  on a.Approval_ID = av.Approval_ID 
  and km.Milestone_Type_ID = av.Milestone_Type_ID
inner join dbo.NETWORK_MILESTONE AS m 
  on A.Approval_ID = m.Approval_ID 
  and km.Milestone_Type_ID = m.Milestone_Type_ID
inner join dbo.REF_MILESTONE AS rm 
  on km.Milestone_Type_ID = rm.Milestone_Type_ID
WHERE EXISTS
(
  SELECT 1 FROM dbo.NETWORK_STATUS 
    WHERE Network_ID = n.Network_ID
    and Status_Type_ID = 2
    --and Status_Type_ID = rm.Status_Type_ID
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.