Q

Compare two many-to-many relationships, Part 1

Is it possible to compare two many-to-many tables in MySql 3.2.3? (I know, my host needs to upgrade.)

Here's my situation... I have five tables: Users, Users2Colors (U2C), Colors, Colors2Flavors (C2F), and Flavors.

USERS +------+--------+ |UserID|UserName| +------+--------+ | 11 | Joe | +------+--------+ 

  
 U2C +------+-------+ |UserID|ColorID| +------+-------+ | 11 | 1 | +------+-------+ | 11 | 2 | +------+-------+ | 11 | 3 | +------+-------+ | 11 | 5 | +------+-------+ | 11 | 6 | +------+-------+ 
  
 COLORS +-------+---------+ |ColorID|ColorName| +-------+---------+ | 1 | red | +-------+---------+ | 2 | orange | +-------+---------+ | 3 | yellow | +-------+---------+ | 4 | green | +-------+---------+ | 5 | blue | +-------+---------+ | 6 | purple | +-------+---------+ 
  
 C2F +-------+--------+ |ColorID|FlavorID| +-------+--------+ | 5 | 301 | +-------+--------+ | 6 | 301 | +-------+--------+ | 3 | 302 | +-------+--------+ | 4 | 302 | +-------+--------+ | 1 | 303 | +-------+--------+ | 5 | 303 | +-------+--------+ 
  
 FLAVORS +--------+----------+ |FlavorID|FlavorName| +--------+----------+ | 301 | raspberry| +--------+----------+ | 302 | lime | +--------+----------+ | 303 | grape | +--------+----------+

I would like to select all the flavors that a user can make with the colors the user has. Thus I need to compare the U2C and C2F tables (both many-to-many). The result should be: Joe can make raspberry and grape. I can get the results by looping through arrays in PHP, but I think that if I could filter my results in the select statement the execution time would be much faster. I've spent several days reading thick books and browsing Web sites, but am still scratching my head. Please help.


That's a long question, and a detailed answer is even longer, which means it will have to be broken up into parts. But that's okay, since there's a natural break point in the development of the answer

First, the complete join to show all Joe's flavors:

select U.UserID , U.UserName , C.ColorID , C.ColorName , F.FlavorID , 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 order by 1,3,5 
  
 11 Joe 1 red 303 grape 11 Joe 3 yellow 302 lime 11 Joe 5 blue 301 raspberry 11 Joe 5 blue 303 grape 11 Joe 6 purple 301 raspberry

Now, extend the join from each flavor to all the colors required for that flavor. Keep the sequence in Joe's color sequence.

select U.UserID , U.UserName , C.ColorID , C.ColorName , F.FlavorID , F.FlavorName , Ctwo.ColorID , Ctwo.ColorName  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,3,5,7 
  
 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

Make sure you understand what's happening in that query before continuing on to Compare two many-to-many relationships (Part 2) of this answer.


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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close