使用外键连接表 | 使用外键连接表

本文介绍了使用外键连接表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我如何INNER JOIN一个包含 2 个外键作为其主键的表?

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 (table1ID CHAR(4), table2ID CHAR(4));

推荐答案

假设您只想按照键的建议将所有内容连接在一起...

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

SELECT *
FROM table1
INNER JOIN table3 on table3.table1ID = table1.table1ID
INNER JOIN MEM_INSTR on MEM_INSTR.table2ID = table3.table2ID

但是假设您有这种情况.

But let's say that you have this scenario.

CREATE TABLE Table1 (
    Table1ID NUMBER,
    Generation NUMBER,
    ...
);

CREATE TABLE Table2 (
    Table2ID NUMBER,
    Table1ID NUMBER,
    Table1Generation NUMBER,
    ...
);

为了论证，假设Table1可以有多个具有相同Table1ID的记录，并且Generation用作辅助键.并且您需要将 Table2 记录加入正确的单个 Table1 记录.您可以像扩展 WHERE 子句一样扩展 ON 子句.

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
INNER JOIN table2 t2
    ON t2.table1id = t1.table1id
    AND t2.table1generation = t1.generation

这篇关于使用外键连接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！