Search multiple listings for missing entries

Question

I have 3 lists, I will make them simple here.

list of letters

B
C

list of numbers
1
2
3

Mixed
A, 1
A, 2
B, 2
B, 3
C 1
C, 3

I need to know what is missing:
A, 3
B, 1
C, 2

The list of letters contains about 85 entries

and the list of numbers has about 500 entries.

The mixed list contains about 75,000 entries.

I can use either a database query (mysql 5.0) or Turbo Delphi 2006 to process text files. What would be the best way to find what is missing?

Thanks
Dave

Answer 1

. :

SELECT c.chr, n.num
FROM chars c, nums n
 WHERE NOT EXISTS (SELECT 1
                     FROM mix m
                    WHERE m.chr = c.chr AND m.num = n.num)

Answer 2

, , SQL:

SELECT
  Letter + ',' + Number AS Combination
FROM
  NumberList,
  LetterList

(, ), NOT EXISTS, , :

SELECT
  Combination
FROM
  AllCombinations AS a
WHERE
  NOT EXISTS 
  (SELECT 1 FROM MyCombitations AS m WHERE m.Combination = a.Combination)

MyCombitations, , , .

, MyCombitations.Combination. .

Answer 3

75.000 . TStringLists. ( ) . . . , .

():

var
  i1, i2: integer;
  sl1, sl2: TStringList;
  cross: array of array of boolean;
begin
  // load data into sl1, sl2
  SetLength(cross, sl1.Count, sl2.Count);
  for i1 := 0 to sl1.Count - 1 do
    for i2 := 0 to sl2.Count - 1 do
      cross[i1, i2] := false;
  // for each element in 'combined' list
    // split it into elements s1, s2
    i1 := sl1.IndexOf(s1);
    i2 := sl2.IndexOf(s2);
    if (i1 < 0) or (i2 < 0) then
      // report error
    else
      cross[i1, i2] := true;
  for i1 := 0 to sl1.Count - 1 do
    for i2 := 0 to sl2.Count - 1 do
      if not cross[i1, i2] then
        // output sl1[i1], sl2[i2]
end;

Answer 4

SELECT letter,number FROM lettersTable l , numbersTable n WHERE
(
    SELECT count(*) 
    FROM 
        (
            SELECT * 
            FROM combinationsTable 
            WHERE l.letter=combinationsTable.letter AND n.number = combinationsTable .number
        ) AS temp
) = 0;

SELECT * FROM A, B, ( -).

Answer 5

(Turbo powers SysTools , ), . :

• ,
• , , " " .
• , ,
• , ( " " ).

" / " , .

:

var
  ListL : tStringList; // the left list
  ListR : tSTringList; // the right list
  ListA : tSTringList; // the Add List (should start empty)
  ListD : tStringList; // the Delete list (should start empty)
  iCurL : integer;     // Left Cursor
  iCurR : integer;     // Right Cursor
  iRes  : integer;     // result of compare
begin
  iCurL := 0;
  iCurR := 0;
  ListL := tStringList.create;
  ListR := tSTringList.create;
  ListA := tSTringList.create;
  ListD := tStringList.create;
  InitAndLoadLists(ListL,ListR,ListA,ListD);
  while (iCurL <= ListL.Count-1) and (iCurR <= ListR.Count-1) do
    begin
      iRes := CompareStr(ListL.Strings[iCurL],ListR.Strings[iCurR]);
      if iRes = 0 then
        begin
          inc(iCurL);
          inc(iCurR);
        end;
      if iRes < 0 then
        begin
          ListA.Add(ListL.Strings[iCurL]);
          inc(iCurL);
        end;
      if iRes > 0 then
        begin
          listD.Add(ListR.Strings[iCurR]);
          inc(iCurR);
        end;
    end;
  while (iCurL <= ListL.Count-1) do
    begin
      listA.Add(ListL.Strings[iCurL]);
      inc(iCurL);
    end;
  while (iCurR <= ListR.Count-1) do
    begin
      listD.Add(ListR.Strings[iCurR]);
      inc(iCurR);
    end;
  ShowMessage( 'ADDS' + ^M+^J + ListA.Text);
  ShowMessage( 'DELS' + ^M+^J + ListD.Text);
end;

The following code is what I used for testing. This is just an example, but in a real situation, I would build my keys so that they are correctly sorted, correctly fill in the numbers and force the case when it was necessary. If you use turbo boost sorting procedures, you can use TWO sort instead of two lists, but the end effect is the same.

procedure InitAndLoadLists(ListL, ListR, ListA, ListD: TStringList);
begin
  ListL.Add('A,1');
  ListL.Add('B,3');
  ListL.Add('C,2');
  ListR.Add('A,2');
  ListR.Add('B,3');
  ListR.Add('C,4');
end;

Edit: Code tested in Delphi 2009 and behaves correctly.