Joining very distant tables in the absence of a direct relationship Consider a database of the following structure:


The schema above is just made up, but assume that this is what the DBA inherits and can't be modified further. Then, hypothetically, if the DBA is asked to provide a selection of the two most distant tables table1 and table7, at least to me, this appears to be no easy feat.

Question: If we wanted to SELECT table1.name, table7.name using a JOIN wouldn't we need a ton of AND clauses (by ton I mean 6)? Would the answer change if the tables were not just 6 tables apart as in my example, but rather were 1,000 tables apart?

I'm not sure if SQL has a special tool for this, or if we just use n-1 number of AND clauses. If the latter is the case, I'd imagine we'd want to write a batch or python file to automate a selection with 999 AND's.
SELECT
    table1.name
    ,table7.name
FROM table1
INNER JOIN table2 on table2_id = table2.id
INNER JOIN table3 on table3_id = table3.id
INNER JOIN table4 on table4_id = table4.id
INNER JOIN table5 on table5_id = table5.id
INNER JOIN table6 on table6_id = table6.id
INNER JOIN table7 on table7_id = table7.id


That is what your query would end up looking like. I would probably use dynamic SQL to piece this together if I needed to regularly.

I would look at the workload on the server. If it happened to be an OLTP server with lots of transactions, it may be semi difficult to adjust things.

If the data does not change regularly or frequently, you could look into creating a mapping table based on master data management / triggers / ETL processes (SSIS / BIML if you are using SQL Server) / Constraints that would piece together a singular table of static data from those tables. Essentially like a physical view. 

Another idea is you can create an indexed view joining those tables together as well.


  Question: If we wanted to SELECT table1.name, table7.name using a JOIN
  wouldn't we need a ton of AND clauses (by ton I mean 6)? Would the
  answer change if the tables were not just 6 tables apart as in my
  example, but rather were 1,000 tables apart?


If you use the old syntax join, yes essentially. I would strongly urge you to use the new join as I show above as it is the new standard and is a) easier to read and b) less prone to error.


  I'm not sure if SQL has a special tool for this, or if we just use n-1
  number of AND clauses. If the latter is the case, I'd imagine we'd
  want to write a batch or python file to automate a selection with 999
  AND's.


There is no 'automated' tool, but I gave some suggestions above. There are other third party BI tools out there that can assist with this, especially those that specialize in master data management / ETL / BI practices.