Join not on original table - SQL Server Forums

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)

Join not on original table

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2008-02-06 : 09:25:02

Hi,

I have three tables:

Advertising - holds information about Advertisments.
AdNotes - holds notes written against each Ad, linked to Advertising through an AdId field.
Administrators - holds name and details of administrators who have access to write notes, joined to AdNotes through a userID field but not associated in any way directly with the Advertising table.

If I run a SELECT query against Advertising then I can see right away how to pull out the latest associated Note:


SELECT * FROM advertising 
LEFT OUTER JOIN AdNotes as an1 ON Ad.adID = An1.adID
WHERE (an1.NotesId = (select max(notesId) from adNotes an2 where an1.adId = an2.adId) or an1.NotesId is null )

However, what I'd like is to be able to pull out the name of the person who wrote the note from Administrators. I can't figure out the syntax to join this table into the query as it has no key to associate it with Advertising. Is it possible?

Cheers,
Matt

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-06 : 09:33:36

Please post the table structure & some sample data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 09:36:30

[code]SELECT * FROM advertising a
LEFT OUTER JOIN (SELECT an1.adId,an2.MaxNotes,ad.UserName
from adNotes an1
inner join (select adId,max(notesId) as MaxNotes
from adNotes
group by adId) an2
on an2.adId=an1.adId
inner join Administrators ad
ON ad.userID=an1.userID
)tmp
on tmp.adId = a.adId[/code]

replace last field with field containing users name in Administrator table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-06 : 10:08:14

[code]SELECT c.adID,
c.UserName,
c.Notes
FROM (
SELECT ad.adID,
a.UserName,
an.Notes,
ROW_NUMBER() OVER (PARTITION BY ad.adID ORDER BY an.adID DESC) AS RecID
FROM Advertising AS ad
LEFT JOIN adNotes AS an ON an.adID = ad.adID
LEFT JOIN Administrators AS a ON a.userID = an.userID
) AS c
WHERE c.RecID = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

mattt
Posting Yak Master

194 Posts

Posted - 2008-02-06 : 10:42:40

Thanks - works like a treat.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources