467,146 Members | 1,010 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,146 developers. It's quick & easy.

JOINS

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
Nov 12 '05 #1
  • viewed: 1144
Share:
1 Reply
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
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by jgalzic@hotmail.com | last post: by
3 posts views Thread by Prem | last post: by
4 posts views Thread by Sri | last post: by
1 post views Thread by Prem | last post: by
4 posts views Thread by michaelnewport@yahoo.com | last post: by
2 posts views Thread by narendra vuradi | last post: by
9 posts views Thread by shapper | last post: by
36 posts views Thread by TC | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.