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)
> )
>
>

Help with One-to-Many query

Given the following two tables:
Parent Child
-- --
ParentID ParentID
ChildID
how can I retrieve the Child row which has the highest value of
ChildID within any single Parent row?
The following does not work.
SELECT * FROM Parent p
INNER JOIN Child c ON p.ParentID = c.ParentID
WHERE c.ChildID = (SELECT MAX(c.ChildID))
Thanks LarsI think you want:
SELECT ParentId, MAX(ChildId) AS ChildId
FROM Child
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars|||Hit send too fast...
SELECT ParentId, MAX(ChildId) AS ChildId
FROM Child
GROUP BY ParentId
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars|||SELECT *
FROM Parent p
INNER JOIN (select child.parentId, max(child.childId) as
childId, <other columns>
from child
group by child.parentId) as c
ON p.ParentID = c.ParentID
and p.childId = c.ChildId
This will work fine if you actually want all rows from parent matched with a
child. We might have to optimize some if you only want a small percentage
of the rows in parent.
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars|||Try,
use northwind
go
select
oh.orderid,
oh.orderdate,
od.max_productid
from
orders as oh
inner join
(
select
orderid,
max(productid) as max_productid
from
[order details]
group by
orderid
) as od
on oh.orderid = od.orderid
-- or
select
oh.orderid,
oh.orderdate,
od.productid
from
orders as oh
inner join
[order details] as od
on oh.orderid = od.orderid
where
od.productid = (select max(a.productid) from [order details] as a where
a.orderid = oh.orderid)
AMB
"larzeb" wrote:

> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars
>|||serveral ways to do it I guess, but you can use a sub-query for instance
SELECT
c.*
FROM
child c
INNER JOIN
(SELECT
parentid
,MAX(childID) ChildID
FROM
parent
GROUP BY
parentid) vt
ON c.childid = vt.childid
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars