SQL Server 2019

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


(No column name)


Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) 
 Jul 25 2020 11:26:55 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE Shipping(
LadReady varchar(10),  
LadJob int ,
LadSeries int);

INSERT INTO   Shipping values
('Shi',22925,410),
('Shi',22925,410),
('Yes',22925,410),
('Shi',22925,100),
('Shi',22925,100),
('Shi',22925,200);


CREATE TABLE Drawings(
Job int,  
Series int,
Status varchar(30),
DShopStatus varchar(30),
SeriesInv varchar(30));

INSERT INTO Drawings  values
(22925,410,'Shop Issued','Complete','Not Invoiced'),
(22925,100,'Shop Issued','Complete','Not Invoiced'),
(22925,200,'Shop Issued','Complete','Invoiced');
9 rows affected

select s.LadReady,s.LadSeries,d.Job,d.Series,d.Status,d.DShopStatus,d.SeriesInv
from Drawings d
inner join Shipping s on d.Job=s.LadJob and d.Series=s.LadSeries
where d.Job=22925
and s.LadReady='Shi'
and d.Status='Shop Issued' 
and d.DShopStatus='Complete'
and d.SeriesInv not in ('Invoiced','Part Invoiced')
and s.LadSeries NOT IN (
                         select distinct Shipping.LadSeries 
                         from Shipping
                          where Shipping.LadReady ='YES' )
group by s.LadReady,s.LadSeries,d.Job,d.Series,d.Status,d.DShopStatus,d.SeriesInv;                     
                          


LadReady
LadSeries
Job
Series
Status
DShopStatus
SeriesInv


Shi
100
22925
100
Shop Issued
Complete
Not Invoiced