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

Viewing 8 posts - 1 through 9 (of 9 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: 26986 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