Views, how to construct multiple joins I have a node activity with fields referencing the node location and a node sport_type. As in, for each activity I need to add a location and a sport type.

On the other hand I also have a node admin with references to location and sport_type. As in, I can create an admin for each sport_type and each location. 

How can I create a view which results in the admin when an activity nid is given? 

In SQL I would write something like:

SELECT  admin.name
FROM    admin
JOIN    activity ON activity.location_id = admin.location_id
        AND activity.sport_type_id = admin.sport_type_id
WHERE   activity.nid = :nid


I try to create a similar query using the ui of views, where the activity_id would be passed in through the url path. I don't know how to implement the join though... Any tips? (Using Drupal 7)
What you're looking for is often called "siblings". 

When sharing the same Taxonomy terms there are Contextual filters Has taxonomy term that can be used and this can work in a lot of cases. 

But there are cases even with shared Taxonomy that needs to go a different route (possibly when using Aggregation or Multilingual sites). That second "sibling" route is also what you need to use when the connection is done through an Entity Reference field that isn't Taxonomy, like it is in your case. 

The solution is done with the help of two Relationships and one Contextual filter. 


Create the Content type Views showing Admin nodes. You first add the Relationship for your ER Location field, this introduces info about the referenced node into the Views. In your case this is the info about Location node referenced from Admin nodes. 
Then you add the reverse Relationship to the first one, it will use the first Relationship by default, this is where the matching happens. The second Relationship introduces info about the content that is using that ER field. In your case this is all the Activity nodes that point to the same Location.  
Add the Contextual filter for Node ID and make it use the second Relationship. Set it the usual way, default value and ID from URL, validate against Activity Content type. 
If this is a Block Display set it to display in a theme region on Activity nodes and adjust its Contextual filter settings. Now when you visit an Activity node you will see listed all the Admin nodes that share the same Location ER. 


Repeat the procedure for the second ER field Sport type. You will have 4 Relationships and 2 Contextual filters in the end. Don't build everything at once, test step by step and build it all for only one ER field first. 

When building for the second ER field you can use the keyword all in Views Preview to "shortcircuit" the first Contextual filter so you can test the second one directly, without interference from the first one (you enter values into Preview separated with / for each Contextual filter you have, for example all/34 to bypass the first Contextual, all/all to bypass both Contextuals, or 34/34 to test for the Activity node with the NID 34). 

This answer has a bit more details for a similar example:  Entity Reference widget selctor in view referring same reference