CODEWITHSUNDEEP
sqlsql-servert-sql
ozfive
ozfive

Reputation: 380

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...

Upvotes: 0

Views: 83

Answers (3)

KumarHarsh
KumarHarsh

Reputation: 5094

;With CTE as
(
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
),
CTE1 as
(
select * from CTE C where Inv_Disp in('P','CH')

)
select * from CTE C
where not exists (select 1 from CTE1 C1 
where c.Order_Num=c1.Order_Num)

Well it depend upon query plan,I use extra CTE to avoid(!=) Inv_Disp != 'I'.

So may be you should put result of CTE in temp table.

Upvotes: 0

lptr
lptr

Reputation: 6798

…

select *
from
(
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,
      max(case when OV.[inv_disp] = 'I' then 0 else 1 end) over(partition by SO.[order_num]) as OrderHasNonI, --..this
      min(case when OV.[inv_disp] = 'I' then 1 else 0 end) over(partition by SO.[order_num]) as OrderHasIOnly --..or that
FROM [Order_View] OV
INNER JOIN [dbo].[SOline] SOL on OV.solineid = SOL.lineid
INNER JOIN [dbo].[SO] SO on SOL.SOQTID = SO.SOQTID
) as t
where OrderHasNonI = 0 -- ... choose one condition of the two (both are equivalent) and the corresponding column(this/that)
or OrderHasIOnly = 1

Upvotes: 1

Dale K
Dale K

Reputation: 27333

Use a CTE which you can then use to check whether a row exists for the order with a "non-I" value. Could be this row or another row.

WITH cte as (
    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
)
SELECT *
FROM cte c1
WHERE NOT EXISTS (SELECT 1 FROM cte c2 WHERE c2.Order_num = c1.Order_Num and Inv_Disp != 'I');

Upvotes: 2

Related Questions