Linking 2 tables to get the last record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Linking 2 tables to get the last record

Author

Topic

bartman_z
Starting Member

11 Posts

Posted - 2005-06-22 : 10:45:41

Guys,

I have 2 tables..

Table1
CallID (int, PK)
Other fields...

Table2
CallDetailID (int, PK)
CallID (int, FK)
DateRaised (datetime)
Description (varchar(500))

I need to create a query based on table1 that is something like

SELECT * FROM Table1 WHERE CallID=3

What I also want in the same query is the latest DESCRIPTION from Table2 that relates to Table1.

i.e.

SELECT Table1.* (and the latest related description from Table2) FROM Table1 WHERE CallID=3

Please help

Many thanks

Bart

www.meridiantd.co.uk

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-22 : 12:44:00

Here is one way.
If this doesn't work, post working DDL/DML and expected results.
(DDL/DML is: create table statements and insert statements)


--this is assuming the Latest description is based on RaisedDate
--and that there could be duplicate RaisedDate values

select	a.CallID
	,a.<OtherColumns> --Use explicit column list instead of *
	,b.Description
from	Table1 a
join	(
	select	top 1 
		CallID
		,Description
	from	Table2 a
	where	CallID = 3
	and	DateRaised in
		(
		select	max(DateRaised) DateRaised
		from	Table2
		where	CallID = a.CallID
		)
	order by callDetailID desc
	) b
		on a.CallID = b.CallID

Be One with the Optimizer
TG

bartman_z
Starting Member

11 Posts

Posted - 2005-06-22 : 13:12:02

TG,

Thanks, nearly worked, but I think I fooled you on specifiying criteria for the CaseID

DDL is
if exists (select * from sysobjects where id = object_id(N'[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [table1]
GO
if exists (select * from sysobjects where id = object_id(N'[table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [table2]
GO

create table table1(
CaseID int,
CaseNo varchar(10)
)

create table table2(
CaseDetailID int,
CaseID int,
Description varchar(200)
)

INSERT INTO table1 (CaseID, CaseNo) VALUES (1, 'ABC123')
INSERT INTO table1 (CaseID, CaseNo) VALUES (2, 'DEF123')
INSERT INTO table1 (CaseID, CaseNo) VALUES (3, 'GHI123')

INSERT INTO table2 (CaseDetailID, CaseID, Description ) VALUES (1, 1, '1 First')
INSERT INTO table2 (CaseDetailID, CaseID, Description ) VALUES (2, 1, '2 Second')
INSERT INTO table2 (CaseDetailID, CaseID, Description ) VALUES (3, 2, '2 First')
INSERT INTO table2 (CaseDetailID, CaseID, Description ) VALUES (4, 2, '2 Second')
INSERT INTO table2 (CaseDetailID, CaseID, Description ) VALUES (5, 3, '3 First')
INSERT INTO table2 (CaseDetailID, CaseID, Description ) VALUES (6, 3, '3 Second')

--SELECT CaseID, CaseNo FROM table1
SELECT a.CaseID, a.CaseNo, b.Description FROM Table1 a INNER JOIN
(SELECT TOP 1 CaseID, Description FROM Table2 a
WHERE CaseID = a.caseid AND CaseDetailID IN
(SELECT MAX(CaseDetailID) CaseDetailID FROM Table2 WHERE CaseID = CaseID)
ORDER BY CaseDetailID DESC) b ON a.CaseID = a.CaseID

drop table [table1]
drop table [table2]

What I get as results from this are;
1 ABC123 3 Second
2 DEF123 3 Second
3 GHI123 3 Second

When what I need are
1 ABC123 1 Second
2 DEF123 2 Second
3 GHI123 3 Second

i.e. the latest CallDetailID for each row from Table1

Thanks in advanced for further help

Bartman

www.meridiantd.co.uk

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-22 : 13:26:09

Ah, switched things up on me, huh? Try this:
by the way, I think the description for max CaseDetailID for CaseID=1 is "2 Second"
(unless I'm missing something)


select	a.CaseID
	,a.CaseNo
	,b.Description
from	Table1 a
join	(
	select	CaseID
		,Description
	from	Table2 a
	where	CaseDetailID in
		(
		select	max(CaseDetailID) CaseDetailID
		from	Table2
		where	CaseID = a.CaseID
		)
	) b
		on a.CaseID = b.CaseID

Be One with the Optimizer
TG

bartman_z
Starting Member

11 Posts

Posted - 2005-06-22 : 13:30:30

Perfect, and you are quite right about the CaseID=1 is "2 Second", my rubbish typing skills.

What a genius, thanks very much

Bart

www.meridiantd.co.uk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources