Use GROUP BY
SELECT t1.id AS id1, t2.id AS id2,
Min(Distance(t1.GEOMETRY,t2.GEOMETRY)) AS DIST
FROM fdo_fc1 AS t1, fdo_fc2 AS t2
GROUP BY t1.id
ORDER BY id1;
Result with my test data:
id1 id2 DIST
1 3 16.278821
2 2 35.114100
3 1 44.721360
4 15 65.734314
5 14 43.289722
6 1 68.029405
7 22 56.727418
8 13 48.301139
9 10 60.876925
10 4 34.205263
11 9 51.224994
12 10 54.817880
13 10 47.853944
14 9 64.124878
15 8 39.560081
16 7 59.304300
17 4 62.169124
I don't understand why your latter query gives only 1x32 results (you have 32 records in the second test table instead of 23, haven't you?). For me it returns the full distance matrix 17x23=391 rows. I made my test tables as you describes with 17 and 23 rows.