mysql - SQL获取所有连接元素，通过连接表指定连接实体

Question

我试图通过相关行之一指定实体，将所有相关行获取到单个实体。

CREATE TABLE entity (
    id INTEGER PRIMARY KEY
)

CREATE TABLE related (
    id INTEGER PRIMARY KEY
    key VARCHAR(32) UNIQUE NOT NULL
    entity_id INTEGER FOREIGN KEY (`entity.id`)
)

我想要特定实体的所有相关行，但我不确定如何进行连接，我总是只获得与我所在的键相关的行用于取消引用。

SELECT 
    *
FROM
    entity e,
    related r
         JOIN
    related r1
 WHERE
     e.id = r.entity_id
         AND r.key= 'lookup key'
         AND r1.entity_id = e.id;

所以如果我执行

INSERT INTO entity VALUES (1, 2);
INSERT INTO related VALUES (1, 'lookup key' 1),
    (2, 'other key' 1), 
    (3, 'another key' 1), 
    (4, 'this key' 2), 
    (5, 'that key' 2), 
    (6, 'their key' 2);

我要

entity.id    related.id    related.key    related.entity_id
1            1             lookup key     1
1            2             other key      1
1            3             another key    1

但我总是得到

entity.id    related.id    related.key    related.entity_id
1            1             lookup key     1   

我不想要这个

entity.id    related.id    related.key    related.entity_id
1            1             lookup key     1
1            2             other key      1
1            3             another key    1
2            4             this key       2
2            5             that key       2
2            6             their key      2

我知道 SQL 并不是 100% 正确。

Answer 1

从逻辑上讲，您真正想做的是通过 id 约束实体，因此有几种不同的方法可以做到这一点，但我认为 correlated subquery 背后的语法和想法是最清晰的之一。

SELECT 
    *
FROM
    entity e,
    JOIN
    related r
 WHERE
     r.entity_id = e.entity_id
     AND e.entity_id IN (SELECT entity_id FROM related WHERE key = 'lookup key')