Inner joins?

I’m trying to get this query to work

SELECT building_id, 
FROM buildings 
INNER JOIN sites ON buildings.site_id = sites.site_id 
INNER JOIN projects ON sites.site_id = projects.site_id 
INNER JOIN assets ON projects.project_id = assets.project_id 
WHERE assets.project_id = 1

I get 2 results, but shouldnt I only get 1?

The assets table only shows 1 with that project_id

i’m sorry, but that makes no sense at all

your results show 3 rows, not 2

but those results could not possibly have come from that query, because those results have a dozen columns, whereas the query is written to return only 2 columns, building_id and name

1 Like

If there are two projects with id=1 you will get two results

1 Like

They’re not the results, from what I can make out of the tiny screen-shot that’s the table contents showing that there’s only one row where project_id = 1. Presuming that’s the assets table, of course.

okay, that makes sense, thanks

too bad we can’t see what’s in the buildings and sites tables

I think I found a better way to get the solution

No this is not a better solution. You should only query from the database what you need and not read all data because you do not know how to write the query and then try to filter the data with the application code.

But we cannot help you as long as you do not tell us your database structure and what your final approach is.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.