To join n tables together, you need a minimum of (n-1) join conditions

[mjsoule]

In Oracle, An Introduction to SQL and PL/SQL, there is a discussion of Joins. The statement follows:

To join n tables together, you need a minimum of (n-1) join conditions.

The use of the words "At Least"
Indicates I can do the following:

where table1.column1 = table2.column2
AND table1.column3 = table2.column4

Any examples of such a thing?

 

[bigscouse]

It is saying that to join tables then the minimum number of conditions is one less than the number of tables.
For example: -

Employee & Manager (2 tables)

WHERE employee.employee_number = manager.employee_number (1)

Employee & Manager & Payroll (3)

WHERE employee.employee_number = manager.employee_number
AND employee.employee_number = payroll.employee_number (2)

You can of course add any number of extra valid conditions, it all depends on what you are joining.

Ged Jones

Top man

 

[shokanson]

A perfect example is a table that has a composite primary key that is referenced by another table. In that case, you would have to join the two tables by specifying each of the columns in the key. For example, assume these two tables:

CREATE TABLE table1 (
t1_key1 NUMBER NOT NULL,
t1_key2 NUMBER NOT NULL,
CONSTRAINT pk_table1 PRIMARY KEY (t1_key1, t1_key2)
)

CREATE TABLE table2 (
t2_key1 NUMBER NOT NULL,
t1_key1 NUMBER NULL,
t1_key2 NUMBER NULL,
CONSTRAINT pk_table2 PRIMARY KEY (t2_key1),
CONSTRAINT fk__t1__t2 FOREIGN KEY (t1_key1, t1_key2)
REFERENCES table1 (t1_key1, t1_key2)
)

To join them, you would have the following query:

SELECT t2.*
FROM table1 t1, table2 t2
WHERE t1.t1_key1 = t2.t1_key1
AND t1.t1_key2 = t2.t1_key2

Two tables, two join conditions. Note that you will have as many join conditions as elements of the composite key.