Joining Tables Indirectly

Transact-SQL
1

I have two tables:

  • TableA
  • TableZ
    Possible also TableH

I would like to join both tables but both do not have a direct field to join them. There can be "n" numbers of joining tables in between. Lets say in this case, we have the following tables. How would I get all these joining tables?

  • TableC
  • TableM
  • TableT

Is there a built-in function such as Hierarchies?

Thanks

2

can you provide sample data for this?
if no column tonjoin them by how are they related to each other?
other than cross apply, which does not required a related column not aure what you are lookingfor

select 
from tablea
xross apply tableb
3

If I am reading your question correctly - and I am not sure at all about that...

There is no 'magic' method of identifying the table relationships unless you have built out the database diagrams. Even then, there is no magic method that will create the joins for you based on a 'starting' table and 'ending' table.

If the relations are TableA -> TableB -> TableC -> TableM -> TableT then you need to include all tables in the join if you want data from TableT in the results.

There is nothing wrong with having 15 tables in the join...if that is what is required to satisfy the question being asked.

4

It there are foreign keys defined you can extrapolate a join "path" using INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE. However if there are multiple paths to join to tables or even with just one path, knowledge of the schema is required.

5

Thanks for all of the replies!

JoTorre, I agree. I did a SYSObject for Parent Child relationship on foreign keys. Was extremely happy that I got a straight hierarchy path using a recursive CTE. However, I soon found out that there are multiple paths to join, especially when it runs into a lookup table.

yosiasz, you gave me an idea to use CROSS JOINs on another question.

Jeff, I was hoping this to be like Windows Explorer folder structure but like JoTorre pointed out, there are multiple paths to the same folder. After this, I agree there is no magic path Jeff. Thanks