Advanced Join Question - 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)

Advanced Join Question

Author

Topic

djfiii
Starting Member

13 Posts

Posted - 2008-05-12 : 14:32:21

I have a query in which I have constructed a bunch of left outer joins, however I need to also join one of the tables on the "right" side of one of those joins with an entirely different table. What I have is as follows:

select F.*, FRL.findingRiskLevel, E.entityName, G.gName, FRS.findingStatus, FCA.findingControlArea, A.aYear, A.aDesc
from Findings F

left outer join Audits A ON A.aID = F.auditID
left outer join Entities E ON E.entityID = F.findingEntity
left outer join Groups G ON G.gID = F.findingGroup
left outer join FindingsReportingStatus FRS ON FRS.findingStatusID = F.findingRepStatus
left outer join FindingsControlAreas FCA ON FCA.findingControlAreaID = F.findingControlArea
left outer join FindingsRiskLevels FRL ON FRL.findingRiskLevelID = F.findingRiskLevel

So I also need to join Audits.aTypeID on AuditTypes.aTypeID and pull back AuditTypes.aType

Any tips appreciated! My attempts thus far have failed me :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-12 : 14:39:06

Your post isn't clear on what type of join you want, so here's my shot at it:

select F.*, FRL.findingRiskLevel, E.entityName, G.gName, FRS.findingStatus, FCA.findingControlArea, A.aYear, A.aDesc, at.aType
from Findings F
left outer join Audits A ON A.aID = F.auditID
left outer join Entities E ON E.entityID = F.findingEntity
left outer join Groups G ON G.gID = F.findingGroup
left outer join FindingsReportingStatus FRS ON FRS.findingStatusID = F.findingRepStatus
left outer join FindingsControlAreas FCA ON FCA.findingControlAreaID = F.findingControlArea
left outer join FindingsRiskLevels FRL ON FRL.findingRiskLevelID = F.findingRiskLevel
inner join AuditTypes at ON A.aTypeID = at.aTypeID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 14:41:57

[code]select F.*, FRL.findingRiskLevel, E.entityName, G.gName, FRS.findingStatus, FCA.findingControlArea, t.aYear, t.aDesc,
t.aType
from Findings F

left outer join
(SELECT A.*,AT.aType
FROM Audits A
INNER JOIN AuditTypes AT
ON A.aTypeID = AT.aTypeID)t
ON t.aID = F.auditID
left outer join Entities E ON E.entityID = F.findingEntity
left outer join Groups G ON G.gID = F.findingGroup
left outer join FindingsReportingStatus FRS ON FRS.findingStatusID = F.findingRepStatus
left outer join FindingsControlAreas FCA ON FCA.findingControlAreaID = F.findingControlArea
left outer join FindingsRiskLevels FRL ON FRL.findingRiskLevelID = F.findingRiskLevel[/code]

djfiii
Starting Member

13 Posts

Posted - 2008-05-12 : 15:28:07

Both of those worked for me. Thanks for the help!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources