joining table on on last entered record

Dear All,

What's the most efficient way of joining a 1 to many relation, where a record in table A will have multiple records in table B.

I'd like to select every record in table A but only joining the last relevant record from table B. So:

Table A:

A1 Prj1
A2 Prj2

Table B:

B1 A1 23/12/2005
B2 A1 26/12/2005
B3 A1 2/1/2007
B4 A2 25/12/2006
B5 A2 1/1/2007

So I'd like to list using the most efficient way this:

A1 Prj1 B3 2/1/2007
A2 Prj2 B5 1/1/2007

I'm assuming this is NOT the most efficient way:

select A, (select top 1 date from B orderBy ...)

Any suggestions?Maybe this:

select A, B
from A
inner join A on A.Aid = B.Aid
where B.date = (select max(B.date) from B where...) ...

this works faster but is there a better way? (I'm sure there is)

Anyone?|||I use this:
select A,
B
from A
inner join --LastRecords
select A,
max(date) as date
from A
group by A) LastRecords
on A.A = LastRecords.A
and A.date = LastRecords.date
...but I can't promise that it is faster.|||Another possibility is:
SELECT A.aid
,A.prj
,MAX(B.DATE)
FROM A
INNER JOIN B ON B.aid = A.aid
GROUP BY A.aid, A.prj
Don't know how this will perform but as long as you have the right index (I'd recomment one on "B.Aid, B.date") I don't think it will differ much between the various methods.|||Thanks for that, how about when the record in table A have no records in table B yet, but I'd still like to list it, but with a NULL value in the columns from table B?|||use a LEFT OUTER JOIN instead of INNER JOIN|||Thanks for that

Join tables

hi
I got a confusing problem.I have 2 tables (Table_1 , Table_2) whit relation
On table_1.key and table_2.fkey.I need to get a table contains information from
2 table : title , key , fkey for mindate,mindate , describtion of mindate.
so I tried to write a function for returning (select top(1) * from table_2 order by date) so only
I need to connect this function to table_1. Here for running function I need to send Key to function
and I dont know how I can do that because wnehe I try to join them I get Error message.in seccond try I made a procedure
like this : SELECT Table_1.title, Table_1.[Key], Table_2.fkey, Table_2.date, Table_2.describtion
FROM Table_1 INNER JOIN
Table_2 ON Table_1.[Key] = Table_2.fkey where Table_2.[key]=(select top(1) Table_2.[key] from table_2 where fkey=Table_1.[key] order by date )

it works perfectly but gets more time to run whene we have 2000 records.Speed goes down ...

table_1:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[title] [nchar](10) COLLATE Arabic_CI_AS NULL,
[Key] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[Key] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

table_2:
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_2](
[date] [datetime] NULL,
[fkey] [int] NULL,
[key] [int] IDENTITY(1,1) NOT NULL,
[describtion] [nchar](10) COLLATE Arabic_CI_AS NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[key] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Table_2] WITH CHECK ADD CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([fkey])
REFERENCES [dbo].[Table_1] ([Key])
GO
ALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_Table_1]

data:
insert into table_1 (title) values('Title1')
insert into table_1 (title) values('Title2')
insert into table_1 (title) values('Title3')

insert into table_2 (date,fkey,describtion) values('2006/10/11',1,'dis 1')
insert into table_2 (date,fkey,describtion) values('2006/10/12',1,'dis 2')
insert into table_2 (date,fkey,describtion) values('2006/10/14',1,'dis 3')
insert into table_2 (date,fkey,describtion) values('2006/10/12',2,'dis 4')
insert into table_2 (date,fkey,describtion) values('2006/10/10',2,'dis 5')
insert into table_2 (date,fkey,describtion) values('2006/10/12',2,'dis 6')
insert into table_2 (date,fkey,describtion) values('2006/10/11',3,'dis 7')
insert into table_2 (date,fkey,describtion) values('2006/10/13',3,'dis 8')
insert into table_2 (date,fkey,describtion) values('2006/10/12',3,'dis 9')
insert into table_2 (date,fkey,describtion) values('2006/10/11',3,'dis 10')

resault :
Title1 1 1 2006-10-11 00:00:00.000 dis 1
Title2 2 2 2006-10-10 00:00:00.000 dis 5
Title3 3 3 2006-10-11 00:00:00.000 dis 7

hai,

First you can create a view

create view mindatelist as
select min(date) as mindate,fkey from Table_2
group by fkey

then run below query..

SELECT Table_1.title, Table_1.[Key], Table_2.fkey, Table_2.date, Table_2.describtion
FROM Table_1
INNER JOIN Table_2 ON Table_1.[Key] = Table_2.fkey
inner join mindatelist on mindatelist.fkey=Table_1.[Key] and Table_2.date=mindatelist.mindate

Next check the performance of this query...

Jefy

|||the resault is rong because we get 2 rows for key # 3
Title1 1 1 2006-10-11 00:00:00.000 dis 1
Title2 2 2 2006-10-10 00:00:00.000 dis 5
Title3 3 3 2006-10-11 00:00:00.000 dis 7
Title3 3 3 2006-10-11 00:00:00.000 dis 10|||

Koosha:

One thing to understand is that your code without the scalar function will run faster than with the scalar function; there is a certain amount of additional overhead that goes with the scalar function pluse the optimizer doesn't optimize scalar functions well. I tried a few things to try to speed up your query. First, I ran your query to get a performance baseline. I then modified the query into what is listed below and tested with the sample data you provided to verify that it qualitatively looked correct.

Next, I generated 2048 entries for table_1 and 32767 entries for table_2 and benchmarked under these circumstances. This is a really small sample set to benchmark with, but I still think the results will at least be indicative. I then compared the modified code to the original code; the time reduction associated with the new code was about 70%; the IO reduction of the new code was about 99%. This does NOT mean that the code is optimized, but it does mean that it is improved -- at least under my particular test circumstances.

Next, I did an experiment to see if a cover index might improve performance. The cover index reduced IO by a very thin 8% or so. Execution time was reduced by about 30%. I would suggest that if this function is not critical that I would probably NOT implement a cover index -- I just don't think it is going to be worth the overhead. Here is the query:

Dave

select t1.title,
t1.[key],
t2.fkey,
t2.date,
t2.describtion
from table_1 t1
inner join
( select [key],
fkey,
date,
describtion,
row_number () over
( partition by fkey
order by date, [key]
) as seq
from table_2
) t2
on t1.[key] = t2.fkey
and t2.seq = 1

|||it works perfectly.thanks for ur helping