CODEWITHSUNDEEP
sqlinner-join
Sterling Archer
Sterling Archer

Reputation: 22395

Joining tables with multiple foreign keys

Considering the following SQL query, I get no results returned. If I remove one of the INNER JOINs, then it returns the data associated to the foreign key.

Notice DefinitionID and TermID are both related to the Primary Key def.ID of the PolicyData table.

SELECT def.* FROM [Questions].[dbo].[PolicyDefinitions] def
INNER JOIN [Questions].[dbo].[PolicyData] p 
ON def.ID = p.TermID
INNER JOIN [Questions].[dbo].[PolicyData] pd 
ON def.ID = pd.DefinitionID

Is it possible to join the tables like this?

Upvotes: 0

Views: 1253

Answers (2)

Caleth
Caleth

Reputation: 62576

You can also do it with just 1 join with an OR 

SELECT 
    def.* 
FROM 
    [Questions].[dbo].[PolicyDefinitions] def
    JOIN [Questions].[dbo].[PolicyData] p ON def.ID = p.TermID OR def.ID = pd.DefinitionID

Upvotes: 1

talegna
talegna

Reputation: 2403

You're actually wanting to use a LEFT JOIN as data will only be returned with your query (using INNER JOIN) when all the joined tables have matching rows, a quick google (other search engines are available;-) ) will link you to many articles on this.

SELECT 
    def.* 
FROM 
    [Questions].[dbo].[PolicyDefinitions] def
    LEFT JOIN [Questions].[dbo].[PolicyData] p ON def.ID = p.TermID
    LEFT JOIN [Questions].[dbo].[PolicyData] pd ON def.ID = pd.DefinitionID

Upvotes: 1

Related Questions