SQL filter rows based on multiple condition and get the matching records

Question

Hello all I have a requirement where I need to filter the rows with multiple conditions and exclude the result if a single entry exists in matching. Here are my sample tables

DECLARE @CUSTOMER TABLE
(
    CUSTOMERID INT,
    CUSTOMERNAME NVARCHAR(100)
)

DECLARE @ORDER TABLE
(
    ORDERID INT,
    CUSTOMERID INT,
    ISSPECIALORDER INT,
    SPECIALORDERID INT
)

DECLARE @SPECIALORDERDTL TABLE
(
    SPECIALORDERID INT,
    SPECIALORDERDATAID INT
)

DECLARE @SPECIALORDERDATA TABLE
(
    SPECIALORDERDATAID INT,
    SPECIALORDERMASTERID INT
)

INSERT INTO @CUSTOMER VALUES (100,'CUSTOMER1'),(200,'CUSTOMER2'),(300,'CUSTOMER3'),(400,'CUSTOMER4`enter code here`')

INSERT INTO @ORDER VALUES (1,100,0,1),(2,100,1,1),(3,100,1,2),(4,200,0,1),(5,200,1,1),(6,200,1,4),(7,300,1,5),(8,400,1,6)

INSERT INTO @SPECIALORDERDTL VALUES(1,1),(2,1),(3,2),(4,4)

INSERT INTO @SPECIALORDERDATA VALUES(1,1),(2,1),(3,1),(4,2),(5,2) -- 2 a special order

SELECT  C.CUSTOMERID,C.CUSTOMERNAME
FROM    @ORDER O
INNER JOIN @CUSTOMER C ON C.CUSTOMERID=O.CUSTOMERID

INNER JOIN @SPECIALORDERDTL SO ON SO.SPECIALORDERID = O.SPECIALORDERID
INNER JOIN @SPECIALORDERDATA SOD ON SO.SPECIALORDERDATAID = SOD.SPECIALORDERDATAID
WHERE SOD.SPECIALORDERID <> 2 AND O.ISSPECIALORDER =0
    GROUP BY C.CUSTOMERID,C.CUSTOMERNAME
    ORDER BY C.CUSTOMERNAME

When I have an entry in @SPECIALORDERDTL with SPECIALORDERMASTERID as 2 I need to consider them as special entries and exclude those. So my query should return only the customer with 100.

Answer 1

It is not clear from your description or SQL what exactly want. From my understanding:

DECLARE @CUSTOMER TABLE
(
    CUSTOMERID INT,
    CUSTOMERNAME NVARCHAR(100)
)

DECLARE @ORDER TABLE
(
    ORDERID INT,
    CUSTOMERID INT,
    ISSPECIALORDER INT,
    SPECIALORDERID INT
)

DECLARE @SPECIALORDERDTL TABLE
(
    SPECIALORDERID INT,
    SPECIALORDERDATAID INT
)

DECLARE @SPECIALORDERDATA TABLE
(
    SPECIALORDERDATAID INT,
    SPECIALORDERMASTERID INT
)

INSERT INTO @CUSTOMER VALUES 
(100,'CUSTOMER1'),
(200,'CUSTOMER2'),
(300,'CUSTOMER3'),
(400,'CUSTOMER4')

INSERT INTO @ORDER VALUES 
(1,100,0,1),
(2,100,1,1),
(3,100,1,2),
(4,200,0,1),
(5,200,1,1),
(6,200,1,4),
(7,300,1,5),
(8,400,1,6)

INSERT INTO @SPECIALORDERDTL VALUES(1,1),(2,1),(3,2),(4,4)

INSERT INTO @SPECIALORDERDATA VALUES(1,1),(2,1),(3,1),(4,2),(5,2) -- 2 a special order

SELECT  C.CUSTOMERID,C.CUSTOMERNAME
from @Customer c
where exists (select * from @ORDER o where o.CustomerId = c.CustomerId)
and not exists (
select * 
from @ORDER O
LEFT JOIN @SPECIALORDERDTL SO ON SO.SPECIALORDERID = O.SPECIALORDERID
LEFT JOIN @SPECIALORDERDATA SOD ON SO.SPECIALORDERDATAID = SOD.SPECIALORDERDATAID
WHERE (SO.SPECIALORDERID IS NULL 
or SOD.SPECIALORDERMASTERID = 2 --AND O.ISSPECIALORDER =0
) AND O.CustomerId = c.CustomerId
);
GO

CUSTOMERID | CUSTOMERNAME
---------: | :-----------
       100 | CUSTOMER1   

db<>fiddle here

Answer 2

Assuming I understand the question, I think a conditional aggregation in the having clause is probably the simplest way to get the result you want:

SELECT C.CUSTOMERID, C.CUSTOMERNAME
FROM @CUSTOMER As C
JOIN @ORDER O 
    ON C.CUSTOMERID = O.CUSTOMERID
JOIN @SPECIALORDERDTL SO 
    ON O.SPECIALORDERID = SO.SPECIALORDERID
JOIN @SPECIALORDERDATA SOD
    ON SO.SPECIALORDERDATAID = SOD.SPECIALORDERDATAID
GROUP BY C.CUSTOMERID, C.CUSTOMERNAME
HAVING COUNT(CASE WHEN SOD.SPECIALORDERMASTERID = 2 THEN 1 END) = 0

The having clause will filter out every customer where at least one of the orders associated with them have a specialordermasterid of 2.

Answer 3

From your description it sounds like not every customer will have an entry in SPECIALORDERDTL or SPECIALORDERDTA so you don't want to inner join to those tables.

What you need is a "not exists" correlated subquery to check that the customers do not have a matching row in those tables.

So you can remove the inner joins to SPECIAL* tables and add something like:-

 where not exists (select null from SPECIALORDERDTL SO where
 SO.SPECIALORDERID = O.SPECIALORDERID and SO.SPECIALORDERMASTERID = 2)

From your description I'm not quite sure where "SOD.SPECIALORDERID <> 2 AND O.ISSPECIALORDER =0" fit into it, so please give further details of outputs if you can't resolve using subquery.

Following your clarification, please try something like this:-

SELECT  distinct C.CUSTOMERID,C.CUSTOMERNAME
FROM    @ORDER O
INNER JOIN @CUSTOMER C ON C.CUSTOMERID=O.CUSTOMERID
where not exists 
(select null from @SPECIALORDERDTL SO 
INNER JOIN @SPECIALORDERDATA SOD ON SO.SPECIALORDERDATAID = SOD.SPECIALORDERDATAID 
where SO.SPECIALORDERID = O.SPECIALORDERID and 
SOD.SPECIALORDERMASTERID = 2
)
order by C.CUSTOMERNAME