2

Consider a database of the following structure: enter image description here

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.

1
  • 1
    Would the answer change if the tables were not just 6 tables apart as in my example, but rather were 1,000 tables apart? It is said that any two people on the planet can be connected by at most 6 other people - the exact number doesn't matter, the point is that I'm having great difficulty imagining a schema that would require any two tables to be a thousand steps apart (world population = 7.6 billion!).
    – Vérace
    Commented May 9, 2018 at 8:00

1 Answer 1

3
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.

2
  • 1
    I suppose you could also simply add a table7_id to table1, and create triggers to make sure this column is populated / up-to-date.
    – dbdemon
    Commented May 9, 2018 at 6:09
  • 1
    A mapping table sounds like the best idea - also known as an associative entity!
    – Vérace
    Commented May 9, 2018 at 7:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.