How to query a one-to-many and only return 1 row on many side

Question

Post reply

How to query a one-to-many and only return 1 row on many side

William Plourde

SSCrazy

Points: 2035
More actions
April 28, 2009 at 10:42 am

#129741

I have a question on how to join 2 tables that have a one-to-many relationship such that I only get the most recent item from the foreign key table. Here is an example.
Table1 - Primary Table
Cols: Id(int,pk), Col1, Col2, Col3, etc..
Table2 - audit/history table. Contains multiple rows for each Table1 row
Table1Key (int,fk)
EventDate
EventText
Question:
How can I construct a query for Table1 that returns the most recent audit date and text from Table2 ?
I am only interested in the most recent item from Table2 but there is a one to many relationship
between T1 and T2, so when I join the tables I get multiple results for each T1 row.
In the past I have accomplished this by using a function to return the top 1 item sorted by date descending and referencing this function in the Table1 query. But is there a better way I can do this, or a way to put this directly into the query without having to encapsulate it in a function ? Thanks.
Bill, Charlotte NC

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Will something like this do what you need:

;with T2 as

(select row_number() over (partition by Table1Key order by EventDate desc) as Row,

Table1Key,

EventDate,

EventText

from dbo.Table2)

select *

from dbo.Table1

left outer join T2

on Table1.ID = T2.Table1Key

and T2.Row = 1;

I've used that kind of solution many times and it works pretty well.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

GSquared (4/28/2009)
Will something like this do what you need:
;with T2 as
(select row_number() over (partition by Table1Key order by EventDate desc) as Row,
Table1Key,
EventDate,
EventText
from dbo.Table2)
select *
from dbo.Table1
left outer join T2
on Table1.ID = T2.Table1Key
and T2.Row = 1;
I've used that kind of solution many times and it works pretty well.

Based on requirements, wouldn't this be an inner join? (I had a solution, but saw that Gus had already posted one, so won't post mine.)

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 3

Another way...

select t1.id, t1.col,d.EventDate,d.EventText

from table1 t1

outer apply (select top 1 t2.EventDate,t2.EventText

from table2 t2

where t2.Table1Key=t1.id order by t2.EventDate desc) d

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

GSquared SSC Guru Points: 260824 More actions · Answer 4

I don't see anything in the requirements about rows in Table1 that don't have a match in Table2, so I left it as an outer join. Maybe I just missed that part, but it's pretty usual to want to know if there's no audit history.

On the Apply version, I've found that's usually a lot slower than the row_number version. Depends on how much data you're looking at, of course.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Could be open to interpretation, but this sure sounds like an inner join to me:

I have a question on how to join 2 tables that have a one-to-many relationship such that I only get the most recent item from the foreign key table.

If there is always a record in table2, left outer join is just as good as inner join. 😉

William Plourde SSCrazy Points: 2035 More actions · Answer 6

Thanks for the replies. I was able to get both examples to work and since I have not been using CTE it was a good excuse to read up on them. And a left outer join is appropriate here because I need all rows from table 1, but there is no guarantee that there will be any history rows in table 2.

fyi..the table 2 history data rows are written via a sp that calls an ssis package that will import between 3 and 5 million rows..but in my ui (c#) I only need to display the information for the most recent import operation.

thanks all.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

William Plourde (4/28/2009)
Thanks for the replies. I was able to get both examples to work and since I have not been using CTE it was a good excuse to read up on them. And a left outer join is appropriate here because I need all rows from table 1, but there is no guarantee that there will be any history rows in table 2.
fyi..the table 2 history data rows are written via a sp that calls an ssis package that will import between 3 and 5 million rows..but in my ui (c#) I only need to display the information for the most recent import operation.
thanks all.

Well, that is good to know. The solution I developed and didn't post used an inner join based on the first line in your original post. 😉

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

Cross Apply or Outer Apply will work.

-- Whups, just realized Mark already submitted this solution.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills