leora leora - 1 year ago 54
SQL Question

trying to inner join multiple tables but always returning 0 rows

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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download