How to join a table based on columns from multiple tables?

Stephen Johnson shared this question 5 years ago
Answered

I'm trying to construct a view but I'm having trouble with a simple join that involves columns from multiple tables:

SELECT *
FROM TableA
JOIN TableB
  ON TableA.col1 = TableB.col1
JOIN TableC
  ON TableA.col2 = TableC.col1
  AND TableB.col2 = TableC.col2
Is this possible with the view builder? I want to avoid using freehand SQL because that seems to throw a bunch of other YellowFin functionality out the window.

Replies (5)

photo
1

Hi Stephen,

if I'm not mistaken that is what is called a "Circular Join" in which case please see the following Community Question on the topic of Circular Joins:

https://community.yellowfinbi.com/topic/how-can-i-remove-circular-joins

and see if the advice there helps you.

thanks,

David

photo
1

I believe you are mistaken. In the example you linked to there are 3 joins (A joined to B joined to C joined to A). In my example there are only two joins. What I want is to add a new join and in the join condition I want to be able to select fields from more than just two tables (as described in my OP). This is really basic SQL.

Our company is trying to transition from raw SQL reports and Excel files to your BI platform, but we're consistently finding that Yellowfin makes things harder rather than easier.

photo
1

Hello Stephen,

Well it turns out your belief that I was mistaken is the correct belief! So I apologise for my mistake, I guess I must have been trying to rush my work too much.

This time I took the time to set up the tables that you described and played around with them to see what is going on, and I think I have found a way to do your joins, but of course will await your confirmation that this is indeed the case.


1) I dragged across a Virtual Table onto the Entity Relationships canvas and joined Table_A to Table_B using the 1st join in your code:


/LLz0+f8B1BwpY9kA3fcAAAAASUVORK5CYIIA


2) Then I dragged across the Table_C and joined it to the Virtual Table using the 2nd join in your code:



I hope that this suggestion meets your reporting requirements, however if it doesn't then please don't hesitate to let me know.

regards,

David

photo
1

Hi Stephen,

just wondering if you've had a chance yet to try the suggestion I came up with, and if so, how did it go?

regards,

David

photo
1

Hi Stephen,

there has been no response for over a month now so this ticket will be closed, however, if you'd ever like to reopen it then all you have to do is to simply add a new post to it and its status will automatically change from Closed to "In Progress" and will appear in my work list.


regards,


David

Leave a Comment
 
Attach a file