Adding Project ID Filter creates a Join Path for Lead Account ID

Question

I am building a table that  I am filtering off Project id = {PROJECT_ID} and when i do it adds the project table, and a Join for Account ID to go to Lead Account ID. But I want only the join where Assignee Account ID is going to Account ID. 
Because of this it's only showing results for me, because I am the Project Lead.

I've tried joining the Project ID to Project ID, etc. I don't want to use SQL if I can avoid it and would prefer the visual builder.

I had this problem random resolve itself on one report I was doing, but I am not sure how to reproduce that. Any tips?

Skyler Ataide · Answer

@Quelyn Thank you for the additional detail! Yes, if you are able to reproduce where Visual SQL is not generating the join path between "Issue"."Assignee account ID" and "Account"."Account ID" this would be helpful to look into further.

In cases where Visual SQL does not produce the desired join path for you, you can always use separate visual mode queries and then merge the two queries. So for this example, you could write a second query that only pulls from the "Account" table and brings in "Assignee account ID" and any other columns of data that you need from this table. You could then merge this account query with your first query using a left join on the "Assignee account ID" column to bring in any data that you need from the Account table. Let me know if this approach makes sense!

Quelyn · Answer

Thanks @Skyler Ataide !
So this works MOST of the time, I will go into the join paths and set Assignee to map to Account ID. Then when I click Project ID in the project table it will map THAT to Project ID in the Issue table. But for some reason, in this one very specific scenario, I could not get it to work.

In this case when I clicked Project ID under Project, it removed the association for Assignee to Account. I'll try to grab a capture if I can reproduce.

Skyler Ataide · Answer

Hey @Quelyn!

Here is some more information on the Visual SQL default join path selection. Visual SQL automatically selects the shortest available join path between tables. However, if there are multiple shortest paths, it may randomly choose one, which might not be the desired join path. If there are multiple possible joins, you can edit the join path in Visual SQL. The "Edit join path" panel shown in your screenshot allows users to:

* View the current join path.
* Select alternative joins or intermediary tables to connect tables

You can interact with a diagram to choose the desired join path by clicking on the gray lines between tables. In this case, you would want to select the gray line that connects "Issue"."Project ID" to "Project"."Project ID" to switch to this join path. You mention that you have already tried joining the Project ID to Project ID, did you do this by selecting this alternative join path shown in your screenshot? If so, what were the results when you tried selecting this alternative join path?