Compare two many-to-many relationships, Part 2

In Part 1 we developed a query which would extract all of Joe's colors, the flavors that each color can make, and from each flavor, all the colors necessary to make that flavor. Here's that data again:

11 Joe 1 red    303 grape     1 red 
11 Joe 1 red    303 grape     5 blue 
11 Joe 3 yellow 302 lime      3 yellow 
11 Joe 3 yellow 302 lime      4 green 
11 Joe 5 blue   301 raspberry 5 blue 
11 Joe 5 blue   301 raspberry 6 purple 
11 Joe 5 blue   303 grape     1 red 
11 Joe 5 blue   303 grape     5 blue 
11 Joe 6 purple 301 raspberry 5 blue 
11 Joe 6 purple 301 raspberry 6 purple 

Now add an additional column, which compares Joe's color to the required color, and comes up with a 1 if it's a match, otherwise a 0. We also sort the data into flavor sequence:

select U.UserID
     , U.UName
     , C.ColorID
     , C.ColorName
     , F.FlavorID
     , F.FlavorName
     , Ctwo.ColorID 
     , Ctwo.ColorName 
     , case when C.ColorID = Ctwo.ColorID 
            then 1 else 0 end 
  from USERS U
inner
  join U2C
    on U.UserID = U2C.UserID
inner
  join COLORS C
    on U2C.ColorID = C.ColorID  
inner
  join C2F
    on C.ColorID = C2F.ColorID 
inner
  join FLAVORS F
    on C2F.FlavorID = F.FlavorID
inner
  join C2Ftwo
    on F.FlavorID = C2Ftwo.FlavorID 
inner
  join COLORS Ctwo
    on C2Ftwo.ColorID = Ctwo.ColorID 
order by 1,5,7
 
11 Joe 5 blue   301 raspberry 5 blue   1 
11 Joe 6 purple 301 raspberry 5 blue   0 
11 Joe 6 purple 301 raspberry 6 purple 1 

11 Joe 5 blue   301 raspberry 6 purple 0 
11 Joe 3 yellow 302 lime      3 yellow 1 
11 Joe 3 yellow 302 lime      4 green  0 
11 Joe 1 red    303 grape     1 red    1 
11 Joe 5 blue   303 grape     1 red    0 
11 Joe 1 red    303 grape     5 blue   0 

11 Joe 5 blue   303 grape     5 blue   1 

This is where the "magic" happens. In effect, we have a partial cross join from each of Joe's colors, to all the colors that are required to make any of the flavors that color can make. The 1 or 0 simply flags whether that specific color matches.

At this point we can do our aggregates. We want to count how many matches Joe's colors had for the required flavor colors, and compare that to the total number of required colors per flavor:

select U.UserID
     , U.UName
     , F.FlavorID
     , F.FlavorName
     , sum( case when C.ColorID = Ctwo.ColorID 
                 then 1 else 0 end ) 
     , count(distinct Ctwo.ColorID) 
  from USERS U
inner
  join U2C
    on U.UserID = U2C.UserID
inner
  join COLORS C
    on U2C.ColorID = C.ColorID  
inner
  join C2F
    on C.ColorID = C2F.ColorID 
inner
  join FLAVORS F
    on C2F.FlavorID = F.FlavorID
inner
  join C2Ftwo
    on F.FlavorID = C2Ftwo.FlavorID 
inner
  join COLORS Ctwo
    on C2Ftwo.ColorID = Ctwo.ColorID      

group
    by U.UserID
     , U.UName
     , F.FlavorID
     , F.FlavorName
 
11 Joe 301 raspberry  2  2 
11 Joe 302 lime       1  2 
11 Joe 303 grape      2  2

Lastly, we rewrite the query so that it just produces the desired answer to the question "Which flavors can Joe make?"

select U.UName
     , F.FlavorName
  from USERS U
inner
  join U2C
    on U.UserID = U2C.UserID
inner
  join COLORS C
    on U2C.ColorID = C.ColorID  
inner
  join C2F
    on C.ColorID = C2F.ColorID 
inner
  join FLAVORS F
    on C2F.FlavorID = F.FlavorID
inner
  join C2Ftwo
    on F.FlavorID = C2Ftwo.FlavorID 
inner
  join COLORS Ctwo
    on C2Ftwo.ColorID = Ctwo.ColorID      
group
    by U.UName
     , F.FlavorName
having sum(case when C.ColorID = Ctwo.ColorID 
                then 1 else 0 end)
     = count(distinct Ctwo.ColorID)

 
Joe raspberry 
Joe grape