Joining 3 tables with the 3rd as optional

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

Transact-SQL (2008)

Joining 3 tables with the 3rd as optional

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-05-24 : 07:03:11

I have 3 tables I want to join together. The first 2 will always have data to show but the 3rd may or may not. I am new to joins and am not sure how to make a 3 table join where it will always show the data from the first 2 tables and only show the data in the third if there is any. Here is the code I has so far, the optional table in this case is the FilePaths table

	SELECT si.SectionID, si.ShortName, si.LinkName, r.RaceID, r.RaceName, r.RaceDescription, r.RaceImagePathID, p.FilePath + p.Name AS FullPath
	FROM dbo.SectionInfo AS si WITH(NOLOCK)
	INNER JOIN dbo.RaceInfo AS r WITH(NOLOCK) 
    ON r.SectionID = si.SectionID
    INNER JOIN dbo.FilePaths as p WITH (NOLOCK)
    ON p.PathID = r.RaceImagePathID

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-05-24 : 07:28:30

Change the last join to a LEFT OUTER JOIN instead:

SELECT si.SectionID, si.ShortName, si.LinkName, r.RaceID, r.RaceName, r.RaceDescription, r.RaceImagePathID, p.FilePath + p.Name AS FullPath
FROM dbo.SectionInfo AS si WITH(NOLOCK)
  INNER JOIN dbo.RaceInfo AS r WITH(NOLOCK) 
    ON r.SectionID = si.SectionID
  LEFT OUTER JOIN dbo.FilePaths as p WITH (NOLOCK)
    ON p.PathID = r.RaceImagePathID

- Lumbago
My blog-> http://thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources