select top value in m-to-m relationship

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 2005 Forums

Transact-SQL (2005)

select top value in m-to-m relationship

Author

Topic

BunnyStrider
Starting Member

4 Posts

Posted - 2010-12-08 : 17:09:40

Ok

Table1 & Table2 have an FK called NoteId

I want the left join (each row from table 1 joined with the best matching row from table 2)
where the best match is defined by the one with the highest PK from 2 (so MAX(Table2.Table2Id))
so something like

select MAX(Table2.Table2Id)
FROM
Table1
LEFT JOIN
Table2
ON Table1.NoteId = Table2.NoteId

But of course I want the whole row from Table2 and I only want rows that satisfy the join and are the max Table2Id row for that NoteId join

Is this explained well enough???

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-08 : 17:45:45

select t1.*,t2.*
from Table1 as t1
left join
(select row_number() over (partition by NoteId order by Table2Id Desc) as rownum, * from Table2) as t2
on t1.NoteId = t2.NoteId and t2.rownum = 1

No, you're never too old to Yak'n'Roll if you're too young to die.

BunnyStrider
Starting Member

4 Posts

Posted - 2010-12-09 : 10:29:40

Wow, that's very close and I already don't know what's going on but it returns all Table2Id's that match and I need only the latest Table2Id row for each NoteId join up.

so for instance what you wrote returns:
table1Id | table2Id | NoteId
1 1 1
1 2 1
1 3 1
2 7 1
2 8 1
2 9 1
2 11 2
2 12 2
2 13 2

but i need it to return like this:
table1Id | table2Id | NoteId
1 3 1
2 9 1
2 13 2

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-09 : 12:11:01

Try to replace the LEFT JOIN by JOIN.

No, you're never too old to Yak'n'Roll if you're too young to die.

BunnyStrider
Starting Member

4 Posts

Posted - 2010-12-09 : 12:22:51

Sorry I keep not being completely clear...
That works, the only problem is I need to know if there's a Table1 entry with no Table2 entries

so to update the previous example:
table1Id | table2Id | NoteId
1 1 1
1 2 1
1 3 1
2 7 1
2 8 1
2 9 1
2 11 2
2 12 2
2 13 2
3 NULL 2
4 NULL 1
4 NULL 2

should be more like this
table1Id | table2Id | NoteId
1 3 1
2 9 1
2 13 2
3 NULL 2
4 NULL 1
4 NULL 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-09 : 12:36:16

[code]SELECT t1.table1Id,t2.table2Id,t1.NoteId
FROM (SELECT DISTINCT table1Id,NoteId FROM Table1) t1
OUTER APPLY (SELECT TOP 1 table2Id FROM Table2 WHERE NoteId = t1.NoteId ORDER BY table2Id DESC) t2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BunnyStrider
Starting Member

4 Posts

Posted - 2010-12-09 : 15:13:41

This is what I have so far and I'm pretty sure it's giving me the data I want correctly. Is this the best way to do what I'm doing??
Thanks for all the help guys.

SELECT
p.lastname + ', ' + p.firstname AS PatientName,
p.mpinumber AS MRN,
m.accountnumber AS AccountNumber,
m.meetingnumber AS CaseId,
m.begintime,
r.roomid AS RoomId,
r.roomname AS RoomName,
m.begintime AS CaseStartTime,
n1.NoteAnswerId,
n1.InUseByUserID,
n1.LockedByUserID,
n2.NoteAnswerId,
n2.InUseByUserID,
n2.LockedByUserID,
n3.NoteAnswerId,
n3.InUseByUserID,
n3.LockedByUserID,
n4.NoteAnswerId,
n4.InUseByUserID,
n4.LockedByUserID,
n5.NoteAnswerId,
n5.InUseByUserID,
n5.LockedByUserID,
n6.NoteAnswerId,
n6.InUseByUserID,
n6.LockedByUserID
FROM
patients p JOIN
meetings m ON p.patientid = m.PatientID JOIN
corooms r ON m.RoomID = r.roomid JOIN
codept d ON r.deptid = d.deptid
OUTER APPLY
(SELECT TOP 1 NoteAnswerId, InUseByUserID, LockedByUserID
FROM NoteAnswerMaster WHERE NoteID = d.Note1ID AND meetingnumber = m.meetingnumber ORDER BY NoteAnswerID DESC) n1
OUTER APPLY
(SELECT TOP 1 NoteAnswerId, InUseByUserID, LockedByUserID
FROM NoteAnswerMaster WHERE NoteID = d.Note2ID AND meetingnumber = m.meetingnumber ORDER BY NoteAnswerID DESC) n2
OUTER APPLY
(SELECT TOP 1 NoteAnswerId, InUseByUserID, LockedByUserID
FROM NoteAnswerMaster WHERE NoteID = d.Note3ID AND meetingnumber = m.meetingnumber ORDER BY NoteAnswerID DESC) n3
OUTER APPLY
(SELECT TOP 1 NoteAnswerId, InUseByUserID, LockedByUserID
FROM NoteAnswerMaster WHERE NoteID = d.Note4ID AND meetingnumber = m.meetingnumber ORDER BY NoteAnswerID DESC) n4
OUTER APPLY
(SELECT TOP 1 NoteAnswerId, InUseByUserID, LockedByUserID
FROM NoteAnswerMaster WHERE NoteID = d.Note5ID AND meetingnumber = m.meetingnumber ORDER BY NoteAnswerID DESC) n5
OUTER APPLY
(SELECT TOP 1 NoteAnswerId, InUseByUserID, LockedByUserID
FROM NoteAnswerMaster WHERE NoteID = d.Note6ID AND meetingnumber = m.meetingnumber ORDER BY NoteAnswerID DESC) n6
WHERE
m.iscancelled = 0 AND
m.isblock = 0 AND
r.deptid = @DepartmentId AND
m.begintime between @Begin and @End
ORDER BY r.roomname, m.begintime, p.lastname, p.firstname

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources