Joining Tables With Foreign Keys

How do I INNER JOIN a table that contains 2 foreign keys as its primary keys? CREATE TABLE table1 (table1ID CHAR(4)); CREATE TABLE MEM_INSTR (table2ID CHAR(4)); CREATE TABLE table3

Solution 1:

Assuming you want to just join everything together as keys suggest...

SELECT*FROM table1
INNERJOIN table3 on table3.table1ID = table1.table1ID
INNERJOIN MEM_INSTR on MEM_INSTR.table2ID = table3.table2ID

But let's say that you have this scenario.

CREATETABLE Table1 (
    Table1ID NUMBER, 
    Generation NUMBER, 
    ... 
);

CREATETABLE Table2 (
    Table2ID NUMBER, 
    Table1ID NUMBER, 
    Table1Generation NUMBER, 
    ...
);

Let's say for argument's sake that Table1 can have multiple records with the same Table1ID, and Generation is used as a secondary key. And you need to join a Table2 record to the correct single Table1 record. You can expand the ON clause the same way you would expand a WHERE clause.

SELECT*FROM table1 t1
INNERJOIN table2 t2 
    ON t2.table1id = t1.table1id 
    AND t2.table1generation = t1.generation

Solution 2:

You join it like you usually do, nothing really special about that. So you go something like this:

SELECT ...
FROM table1
INNERJOIN table3 ON table3.table1ID = table1.table1ID
INNERJOIN MEM_INSTR ON MEM_INSTR.table2ID = table3.table2ID