First, I'm just asking about inner joins. And, I'm thinking self joins or reflexive joins through a join table are special cases which depend on how the data is structured (e.g. city – state – country, child – parent – grandparent).
But for the rest of joining tables there's no need to repeat a table in any given "chain"?
(What I mean by "chain":
__companies -- locations AS L1 -- N Joined Tables -- locations AS L2
SELECT people |
__schools -- locations AS L3
So, as best I can figure there is a necessary duplication of L1 and L3 since they're unconnected searches. But any conditions on a search of L2 could be placed on a search of L1.
Is this universally true? Is this generally true?
Best Answer
Dependent on context (chain) addressing to the same table can mean different sets of rows. So you have to point them by two different aliases to avoid ambiguity.
Let's imagine we want to build the list of customers and their suppliers both with cities where they reside:
Here same table is joined twice because each join performed on it's own condition and produce different resulting city names - one for customer and one for supplier. And there is no way to achieve the desired result without joining the same table twice.
P.S.
Let's imagine we have the list of cities visited by tourist:
Here
PREVIOUS_ID
is the reference to the row in the same table and it mean " the city tourist come from". And now we want to build the route containing last four visited cities to the some point:As you can see we can easely join again and again the same tables.