Q

Compare two many-to-many relationships, Part 2

See Compare two many-to-many relationships (Part 1) for the question.

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

This was first published in January 2004

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close