sqlite - 从间接关系中获取数据

Question

我有 4 个 sqlite 表 A、B、C、D。

三关系表

• 表 A,B 上的 A_B_Rel
• 表 A,C 上的 A_C_Rel
• 表 C,D 上的 C_D_Rel

如何在表 B 和 D 上建立关系？

标签： sqlite

Answer 1

我相信以下可能是您想要的：-

SELECT * FROM b 
    JOIN A ON a_b_rel = a.id 
    JOIN C ON a_c_rel = a.id 
    JOIN D ON c_d_rel = c.id
;

因此，您从B获取一行，然后将其与A连接，以便您可以从C连接相关行，以便您可以从D获取相关行。

例如 ：-

DROP TABLE IF EXISTS a;
DROP TABLE IF EXISTS b;
DROP TABLE IF EXISTS c;
DROP TABLE IF EXISTS d;
CREATE TABLE IF NOT EXISTS a (id INTEGER PRIMARY KEY, name_a TEXT);
CREATE TABLE IF NOT EXISTS b (id INTEGER PRIMARY KEY, a_b_rel INTEGER, name_b TEXT);
CREATE TABLE If NOT EXISTS c (id INTEGER PRIMARY KEY, a_c_rel INTEGER, name_c TEXT);
CREATE TABLE IF NOT EXISTS d (id INTEGER PRIMARY KEY, c_d_rel INTEGER, name_d TEXT);
INSERT INTO a (name_a) VALUES
    ('FRED'),
    ('BERT'),
    ('TOM');
INSERT INTO b (a_b_rel,name_b) VALUES 
    (1,'REFERENCES FRED'),
    (1,'ALSO REFERENCES FRED'),
    (1,'REFERENCES FRED YET AGAIN'),
    (2,'REFERENCES BERT'),
    (2,'ANOTHER REFERENCE TO BERT'),
    (3,'LOOKS AT TOM'),
    (3,'LINKED TO TOM'),
    (3,'MAPPED TO TOM');
INSERT INTO c (a_c_rel,name_c) VALUES
    (1,'C MAPS TO FRED'),
    (1,'ANOTHER C MAP TO FRED'),
    (2,'C LOOKING AT BERT'),
    (3,'C LOOKING AT TOM');
INSERT INTO d (c_d_rel,name_d) VALUES
  (1,'D SEES C MAPS TO FRED'),
    (1,'ANOTHER D LOOKING AT C MAPS TO FRED'),
    (4,'D SEES C MAPPED TO TOM');
SELECT * FROM b 
    JOIN a ON a_b_rel = a.id 
    JOIN c ON a_c_rel = a.id 
    JOIN d ON c_d_rel = c.id
;

结果是 ：-