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.


    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top