EXPERT RESPONSE
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.
|
