trying to inner join multiple tables but always returning 0 rows.

Question

I am trying to figure out why this query returns 0 rows as there is data in all 3 tables.

Here are my tables:

Table1: Applications Columns: ID, Name

Table2: Resources
Columns: ID, Name

Table3: ApplicationResourceBridge
Columns: ID, app_id, resource_id

And Here is the query

SELECT       Resources.name
,            ApplicationResourceBridge.resource_id AS Expr3
FROM         Resources 
INNER JOIN   Applications 
ON           Resources.id = Applications.id 
INNER JOIN   ApplicationsResources 
ON           Resources.id = ApplicationResourceBridge.resource_id

Answer 1

Change this:-

 Applications ON Resources.id = Applications.id INNER JOIN

to this:-

 Applications ON Applications.id = ApplicationResourceBridge.app_id INNER JOIN

You were trying to join the Appliciation ID to the Resource ID but these have no relationship. What you really want is to join the Application table to the Bridge table and the Resource table to the Bridge table.

Answer 2

Your current query tries to match Resources.id with Applications.id, but they're different things. It should match Applications.id with ApplicationResources.app_id.

It's generally clearer to have the bridge table as the middle join, so it looks like a link. For example:

SELECT       Resources.name
,            ar.resource_id
FROM         Resources r
INNER JOIN   ApplicationsResources ar
ON           r.id = ar.resource_id
INNER JOIN   Applications a
ON           a.id = ar.app_id

Answer 3

SELECT r.Name AS "Resource Name" , a.Name AS "Application Name"
FROM ApplicationResourceBridge as b
INNER JOIN Resources as r ON r.ID = b. resource_id
INNER JOIN Applications as a ON a.ID = b.app_id

UPDATE:
When constructing FROM ... JOIN ... JOIN ... follow "the path" do not "jump over".

Answer 4

The fact that all three have data in them is immaterial. The question is whether there is data in "Resources" that has the same ID as rows in BOTH "Applications" and "ApplicationResourceBridge".

Update: don't want to post this as if I thought of it but Andomar makes a good point that you seem to be attempting to link the wrong tables fields. Thus, my answer might be right in the abstract but his might explain why you aren't getting field matches.