How to select records with a combination of values exist between two tables

I cannot figure this one out, I want to check these two tables, where ALL LadReady are ‘Shi’ for a given LadSeries. This query is almost there… but 410 should not be in the results because there is clearly one record where LadReady is ‘Yes’ instead of ‘Shi’.

Query:

SELECT     Shipping.LadReady, 
           Shipping.LadSeries, 
           Drawings.Job, 
           Drawings.Series, 
           Drawings.Status, 
           Drawings.DShopStatus, 
           Drawings.SeriesInv 
FROM Shipping 
LEFT JOIN Drawings ON Drawings.Job = Shipping.LadJob 
AND Drawings.Series = Shipping.LadSeries 
WHERE Drawings.Job='22925' 
AND Shipping.LadReady='Shi' 
AND Drawings.Status='Shop Issued' 
AND DShopStatus='Complete'
AND (NOT Drawings.SeriesInv='Invoiced' AND NOT SeriesInv='Part Invoiced')

Table: Shipping

LadReady | LadJob | LadSeries 
-----------------------------
Shi      | 22925  | 410
Shi      | 22925  | 410
Yes      | 22925  | 410
Shi      | 22925  | 100
Shi      | 22925  | 100
Shi      | 22925  | 200

Table: Drawings

Job   | Series |  Status      | DShopStatus | SeriesInv
-------------------------------------------------------
22925 | 410    | Shop Issued  | Complete    | Not Invoiced
22925 | 100    | Shop Issued  | Complete    | Not Invoiced
22925 | 200    | Shop Issued  | Complete    | Invoiced

Results:

 LadReady | LadSeries| Job   | Series | Status       | DShopStatus | SeriesInv
--------------------------------------------------------------------------------
Shi       | 410      | 22925 | 410    | Shop Issued  | Complete    | Not Invoiced
Shi       | 100      | 22925 | 100    | Shop Issued  | Complete    | Not Invoiced

The first result above, 410 should NOT be there because we have one record that is LadReady=’Yes’ instead of ‘Shi’.

Answer

Try the following query. first restrict the shipping rows to be only the valid ones, then join it to drawings

with valid as (
    select Max(ladready) ladready, ladjob, ladseries
    from shipping
    where ladjob=22925
    group by ladjob, ladseries
    having Count(*)=Count(case when ladready='Shi' then 1 end)
)
select v.LadReady, v.LadSeries, d.Job, d.Series, d.DShopStatus, d.SeriesInv
from valid v
join drawings d on d.series=v.ladseries 
    and d.DShopStatus='complete' 
    and d.status='shop issued'
    and not (d.SeriesInv in ('Invoiced','Part Invoiced'))