Help with one-to-many relation join

I have two tables defined below. I would like to join them but choose
only the record from tblb which has the highest value of BID.
I know the following is wrong and I need help. Thanks, larzeb.
SELECT MAX(A), MAX(B), MAX(C), MAX(D), MAX(Y), MAX(Z)
FROM tbla A
JOIN tblb B
ON A.AID = B.AID
GROUP BY A.AID
CREATE TABLE tbla (
AID int IDENTITY(1,1) NOT NULL ,
A char (10),
B char (10),
C char (10),
D char (10),
CONSTRAINT PK_ID PRIMARY KEY (AID)
)
CREATE TABLE tblb (
BID int IDENTITY(1, 1) NOT NULL ,
AID int NOT NULL ,
Y char (10),
Z char (10) ,
CONSTRAINT PK_B PRIMARY KEY (BID),
CONSTRAINT FK_B_A FOREIGN KEY (AID)
REFERENCES tbla (AID)
)Here are a few possibilities:
select A, B, C, D, Y, Z
from tbla A
join tblb B
on A.AID = B.AID
where not exists (
select * from tblb as B2
where B2.AID = B.AID
and B2.BID < B.BID
)
or
select A, B, C, D, Y, Z
from tbla A
join tblb B
on A.AID = B.AID
where B.BID in (
select max(BID) from tblb as B2
group by B2.AID
)
or
select A, B, C, D, Y, Z
from tbla A
join tblb B
on A.AID = B.AID
where B.BID = (
select max(BID)
from tblb as B2
where B2.AID = B.AID
)
Steve Kass
Drew University
larzeb wrote:

>I have two tables defined below. I would like to join them but choose
>only the record from tblb which has the highest value of BID.
>I know the following is wrong and I need help. Thanks, larzeb.
>SELECT MAX(A), MAX(B), MAX(C), MAX(D), MAX(Y), MAX(Z)
>FROM tbla A
>JOIN tblb B
> ON A.AID = B.AID
>GROUP BY A.AID
>CREATE TABLE tbla (
> AID int IDENTITY(1,1) NOT NULL ,
> A char (10),
> B char (10),
> C char (10),
> D char (10),
> CONSTRAINT PK_ID PRIMARY KEY (AID)
> )
>CREATE TABLE tblb (
> BID int IDENTITY(1, 1) NOT NULL ,
> AID int NOT NULL ,
> Y char (10),
> Z char (10) ,
> CONSTRAINT PK_B PRIMARY KEY (BID),
> CONSTRAINT FK_B_A FOREIGN KEY (AID)
> REFERENCES tbla (AID)
> )
>
>