JOINS

[Terry]

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

[Knut Stolze]

Terry wrote:

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 ...
FROM A JOIN ( SELECT ...
FROM B
WHERE b <= ALL ( SELECT b
FROM B ) ) AS B ON
( 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.

Then you need an OUTER JOIN - either LEFT OUTER JOIN or RIGHT OUTER JOIN (I
always mix up in which table the rows are preserved if there are no
matching rows in the other table).

--
Knut Stolze
Information Integration
IBM Germany / University of Jena