mysql - 使用 mysql 查询从表中选择所有行，并指示存在于另一个表中

Question

我有三个表

Table a
+-----+-------+
| aid | value |
+-----+-------+
|   1 | A     |
|   2 | B     |
|   3 | C     |
|   4 | D     |
+-----+-------+

Table b
+-----+------+
| bid | name |
+-----+------+
|   1 | A    |
|   2 | B    |
|   3 | C    |
+-----+------+

Table ba (mapping of table a and table b)
+-----+-----+
| bid | aid |
+-----+-----+
|   1 | 1   |
|   2 | 1   |
|   3 | 1   |
|   3 | 2   |
|   1 | 3   |
|   2 | 3   |
|   2 | 4   |
+-----+-----+

从这些表中我想要一个类似的查询

SELECT aid, mapped('true'-if(aid exist in ba) 'false'-otherwise) 
FROM a 
     JOIN b 
     JOIN ba 
WHERE bid=1

从我可以生成列表的地方得到结果

(when bid=1)
A-mapped
B-not mapped
C-mapped
D-not mapped

(when bid=2)
A-mapped
B-not mapped
C-mapped
D-mapped

(when bid=3)
A-mapped
B-mapped
C-not mapped
D-not mapped

现在我在 while 循环中为表 'a' 的所有行生成列表，并在循环内为每次迭代执行查询以检查表 'ba' 中是否存在。

Answer 1

我认为这应该是表 b 独立的:

SELECT CONCAT_WS('-', a.value,  IF(ba.aid IS NULL, "-not mapped", "-mapped"))
FROM a LEFT JOIN ba ON a.aid = ba.aid AND ba.bid = 1
ORDER BY a.aid

注意:我将“a”表作为基表，因为您的示例包含“a”表中的所有值。