Return records that only have a certain status from a select query that includes multiple joins

I have a query:

SELECT OV.[inv_disp] AS Inv_Disp
,OV.[solineid] AS SO_Line_ID
,SO.[order_num] AS Order_Num
,SOL.[SOID] AS SO_ID
,SOL.[OrderDate] AS Order_Date
FROM [Order_View] OV
INNER JOIN [dbo].[SOline] SOL on OV.solineid = SOL.lineid
INNER JOIN [dbo].[SO] SO on SOL.SOQTID = SO.SOQTID


That returns records such as these:

Inv_Disp SO_Line_ID Order_Num SO_ID Order_Date
I 3417119 2003766 2254221 2021-03-31 00:00:00.000
I 3422822 2007943 2258398 2021-04-05 00:00:00.000
I 3422823 2007943 2258398 2021-04-05 00:00:00.000
P 3423523 2008468 2258923 2021-04-06 00:00:00.000
I 3423524 2008468 2258923 2021-04-06 00:00:00.000
I 3423522 2008322 2258211 2021-04-04 00:00:00.000
CH 3423521 2008321 2258210 2021-04-03 00:00:00.000


I want to be able to only return records that only have 'I' values under Inv_Disp where the Order_Num may or may not be the same. But where if an Order_Num Inv_Disp contains an 'I' AND some other value such as 'CH' or 'P' that it would not return the records for that Order_Num at all.

So the final output should only return:

Inv_Disp SO_Line_ID Order_Num SO_ID Order_Date
I 3417119 2003766 2254221 2021-03-31 00:00:00.000

I 3422822 2007943 2258398 2021-04-05 00:00:00.000
I 3422823 2007943 2258398 2021-04-05 00:00:00.000

I 3423522 2008322 2258211 2021-04-04 00:00:00.000


It should not return

Inv_Disp SO_Line_ID Order_Num SO_ID Order_Date
P 3423523 2008468 2258923 2021-04-06 00:00:00.000
I 3423524 2008468 2258923 2021-04-06 00:00:00.000

CH 3423521 2008321 2258210 2021-04-03 00:00:00.000


Because I have the two INNER JOINS do I need to SELECT INTO a temp table and then query that temp table or can I just directly query the query somehow? I am probably over-complicating this...

Continue reading...