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

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

CodePudding user response：

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'))

CodePudding user response：

You can use a cte:

with cte(id, c, s) as (
   select s.ladseries, count(*), sum(s.ladready="Shi") from shipping s group by s.ladseries
)
select "Shi", d.* from drawings d join cte c on d.series = c.id and c.c = c.s where d.seriesinv = 'Not Invoiced'

CodePudding user response：

I don't know which dbms are you using, this is tested on MySQL and SQL Server.

Add below condition to remove all the LadSeries who are at least one time Yes

      Shipping.LadSeries NOT IN (
                                select distinct Shipping.LadSeries 
                                from Shipping
                                where Shipping.LadReady ='YES' );


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;

Demo MySQL: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/177

Demo SQL Server: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=784990a572531ef9d4063926d8e55ec1