One to Many Lists Merge

Approach using v10 functions:

CustomMerge1[lists__] := Module[{assocList, keys, vals, res},
   assocList = GroupBy[#, First -> Rest] & /@ {lists};
   keys = Sort@DeleteDuplicates[Flatten[Keys /@ assocList]];
   res = Table[
     vals = #[k] /. Missing[___] -> {{Missing[], Missing[]}} & /@ 
       assocList;
     Flatten /@ Tuples[{{k}, Sequence @@ vals}]
     , {k, keys}];
   Flatten[res, 1]
   ];
CustomMerge1[list1, list2, list3]
(*{{1, A, AA, a, aa, Missing[], Missing[]}, {1, A, AA, b, bb, Missing[],
   Missing[]}, {1, A, AA, c, cc, Missing[], Missing[]}, {2, B, BB, d, 
  dd, 0.2, 20}, {2, B, BB, e, ee, 0.2, 20}, {3, C, CC, f, ff, 0.3, 
  30}, {3, C, CC, g, gg, 0.3, 30}, {3, C, CC, h, hh, 0.3, 30}, {4, D, 
  DD, i, ii, 0.4, 40}, {4, D, DD, j, jj, 0.4, 40}, {4, D, DD, 4, kk, 
  0.4, 40}, {4, D, DD, l, ll, 0.4, 40}, {4, D, DD, m, mm, 0.4, 
  40}, {5, Missing[], Missing[], n, nn, 0.5, 50}, {5, Missing[], 
  Missing[], o, oo, 0.5, 50}, {6, F, FF, Missing[], Missing[], 0.6, 
  60}, {7, Missing[], Missing[], p, pp, Missing[], Missing[]}}*)

As you see this function work with any number of input lists


First augment each list to fill in any Missing items. Then work through fulllist2 to insert matching items from fulllist1. To complete the extra credit part of your question you could use a similar method to join corresponding elements of your list1 and list3 before before or after doing the augmentation and use this result in the join process with fulllist1.

list1 = {{1, A, AA}, {2, B, BB}, {3, C, CC}, {4, D, DD}, {6, F, FF}};
list2 = {{1, a, aa}, {1, b, bb}, {1, c, cc}, {2, d, dd}, {2, e, ee}, {3, f, ff},
  {3, g, gg}, {3, h, hh}, {4, i, ii}, {4, j, jj}, {4, k, kk}, {4, l, ll},
  {4, m, mm}, {5, n, nn}, {5, o, oo}, {7, p, pp}};

u = Union[Map[First, Join[list1, list2]]];
fulllist1 = Join[list1, Map[{#,Missing[], Missing[]}&, Complement[u, Map[First, list1]]]];
fulllist2 = Join[list2, Map[{#, Missing[], Missing[]}&, Complement[u, Map[First, list2]]]];
Sort[Map[Join[#, Rest[First[Cases[fulllist1, {First[#], __}]]]] &, fulllist2]]
 {{1, a, aa, A, AA}, {1, b, bb, A, AA}, {1, c, cc, A, AA}, {2, d, dd, B, BB},
  {2, e, ee, B, BB}, {3, f, ff, C, CC}, {3, g, gg, C, CC}, {3, h, hh, C, CC},
  {4, i, ii, D, DD}, {4, j, jj, D, DD}, {4, k, kk, D, DD}, {4, l, ll, D, DD},
  {4, m, mm, D, DD}, {5, n, nn, Missing[], Missing[]},
  {5, o, oo, Missing[], Missing[]}, {6, Missing[], Missing[], F, FF},
  {7, p, pp, Missing[], Missing[]}}

Edit

Code to merge your three lists

list1 = {{1, A, AA}, {2, B, BB}, {3, C, CC}, {4, D, DD}, {6, F, FF}};
list2 = {{1, a, aa}, {1, b, bb}, {1, c, cc}, {2, d, dd}, {2, e, ee}, {3, f, ff},
  {3, g, gg}, {3, h, hh}, {4, i, ii}, {4, j, jj}, {4, k, kk}, {4, l, ll},
  {4, m, mm}, {5, n, nn}, {5, o, oo}, {7, p, pp}};
list3 = {{2, .2, 20}, {3, .3, 30}, {4, .4, 40}, {5, .5, 50}, {6, .6, 60}};

u = Union[Map[First, Join[list1, list2, list3]]];
fulllist1 = Join[list1, Map[{#, Missing[], Missing[]} &, Complement[u, Map[First, list1]]]];
fulllist2 = Join[list2, Map[{#, Missing[], Missing[]} &, Complement[u, Map[First, list2]]]];
fulllist3 = Join[list3, Map[{#, Missing[], Missing[]} &, Complement[u, Map[First, list3]]]];
Sort[Map[Join[#, Rest[First[Cases[fulllist1, {First[#], __}]]], 
  Rest[First[Cases[fulllist3, {First[#], __}]]]] &, fulllist2]]

As always there are at least a dozen different ways of writing anything in Mathematica.


This seems to me cleaner than other options presented:

listMerge[a_List, b_List, pad_: Missing[]] :=
  Lookup[
    # -> {##2} & @@@ a,
    b[[All, 1]],
    pad & /@ a[[1, 2 ;;]]
  ] // Join[b, #, 2] &

Test:

{{1, a, aa, A, AA}, {1, b, bb, A, AA}, {1, c, cc, A, AA}, {2, d, dd, B, BB},
 {2, e, ee, B, BB}, {3, f, ff, C, CC}, {3, g, gg, C, CC}, {3, h, hh, C, CC},
 {4, i, ii, D, DD}, {4, j, jj, D, DD}, {4, k, kk, D, DD}, {4, l, ll, D, DD},
 {4, m, mm, D, DD}, {5, n, nn, Missing[], Missing[]}, {5, o, oo, Missing[],
   Missing[]}, {7, p, pp, Missing[], Missing[]}}

You can use the third parameter to specify a different padding element.