Hi All,

This is a simple problem I expect, but I cant seem to crack it.

I have to tables I wish to join A & B. I wish to join them where A.a =
B.a. There are multiple entries for B.a, and I wish to join to the
rows with the lowest B.b.

SELECT
A.a
MIN(B.b)
FROM
A
LEFT JOIN B
ON (A.a = B.a)
GROUP BY
A.a

That works fine but I also want to extract the other columns from B on
the row that is matched

i.e.
A    B
.a    .a    .b    .c
1         1      5    8
2    1      8    2
    2      7    9
    2      1    7

So I want to obtain

A.a       B.a      B.b     B.c
1         1         5       8
2         2         1       7

An alternative is to use the WHERE instead of the JOIN

SELECT
A.a
B.b
B.c
MIN(B.b)
FROM
A
,B
WHERE
A.a = B.a
AND B.c = (SELECT MIN(B.c) FROM B WHERE A.a = B.a)

But in some instance there is no match at all in B, and 'where' will
lost the A information. Additionally I have a number of row from B  I
need to return;B.d, B.e etc.

Any suggestions

Thanks

Terry