SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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 @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
);

CUSTOMERID	CUSTOMERNAME
100	CUSTOMER1