I have a very large table, part of which looks like this. In the 'Recipes' column are recipes used at two factories listed in the 'Site' column. I'm wanting to perform a check to see if either all recipes produced at site B are also produced at site A, or if site B has some 'distinct' recipes that only it uses.

CodePudding user response：

It seems you're looking for something like this. The query returns the distinct Recipes of Site 'B' for which there are no corresponding (same) Recipes of Site 'A'

select distinct vlt.Recipe
from VeryLargeTable vlt
where vlt.[Site]='B'
      and not exists(select 1
                     from VeryLargeTable vltt
                     where vltt.Recipe=vlt.Recipe
                           and vltt.[Site]='A');

CodePudding user response：

While not necessarily intuitive, GROUP BY does magics in scenarios like this.

Here is a simple, direct answer to the question

select     Recipe
        
from       t

group by   Recipe

having     count(case Site when 'B' then 1 end) > 0
       and count(case Site when 'A' then 1 end) = 0

The following query will give you a detailed view

select   Recipe
        ,max(case Site when 'A' then 'V' end) as A_exists
        ,max(case Site when 'B' then 'V' end) as B_exists
        ,count(*)                             as total_count
        ,count(case Site when 'A' then 1 end) as A_count
        ,count(case Site when 'B' then 1 end) as B_count
        
from     t

group by Recipe

The following query will give you a summarized view

with t2 as
(
    select   max(case Site when 'A' then 'V' end) as A_exists
            ,max(case Site when 'B' then 'V' end) as B_exists
        
    from     t

    group by Recipe
)
select      A_exists
           ,B_exists
           ,count(*) as total_Recipes

from        t2

group by    A_exists
           ,B_exists

fiddle
Please note that I added some rows to the sample in order to make it more interesting