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, NameTable3: ApplicationResourceBridge
Columns: ID, app_id, resource_idAnd 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
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
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".
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.
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.
精彩评论