MySQL 查询：选择链接表的行并捕获“更大”的行数据集

Question

我试图弄清楚如何构建满足以下条件的查询。

我有两张桌子。表a 存储作者列表。表b 存储书籍列表。我有一个链接表c，它将每个作者映射到一本或多本书。当然，一本书可以有多个作者。给定一个作者的名字（让我们取名字=“道格拉斯·亚当斯”），我知道如果我这样做，

SELECT * FROM linktable
    INNER JOIN a ON linktable.a_id = a.id
    INNER JOIN b ON linktable.p_id = b.id
WHERE a.name = 'Douglas Adams';

我就会得到道格拉斯·亚当斯写的所有书籍。让我们假设，道格拉斯·亚当斯有时有“合著者”。我如何获得它们？

我想要一个看起来像这样的列表：

Douglas Adams, The Hitchhiker's Guide to the Galaxy, maybe more details...
Douglas Adams, Book_2, maybe more details...
Coauthor_1, Book_2, same Details as in "Douglas Adams, Book_2, maybe more details..."

这可行吗？

---

我创建了 3 个表，它们映射了我想要存储的内容和想要检索的内容。

2 个存储表是：

CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`),
  KEY `name_INDEX` (`name`),
  FULLTEXT KEY `name_FULLTEXT` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=932723 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title_fulltext` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=1617432

和一个链接上面 2 个表的三分之一表。

CREATE TABLE `linktable` (
  `a_id` int(11) NOT NULL,
  `b_id` int(11) NOT NULL,
  KEY `a_id_INDEX` (`a_id`),
  KEY `b_id_INDEX` (`b_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

SELECT a2.Name, b.title
    FROM a
        INNER JOIN linktable lt
            ON a.id = lt.a_id
        INNER JOIN b
            ON lt.b_id = b.id
        INNER JOIN linktable lt2
            ON lt.b_id = lt2.b_id
        INNER JOIN a a2
            ON lt2.a_id = a2.id
    WHERE a.Name = 'Douglas Adams'
    ORDER BY b.title,
             /* Case Statement so Douglas Adams sorts before other authors */
             CASE WHEN a2.Name = 'Douglas Adams' THEN 1 ELSE 2 END,
             a2.Name

Answer 2

这应该有效：

SELECT * FROM b where b.id in(SELECT c.b_id 
FROM a,c
WHERE a.author = "Douglas Adams"
AND a.id= c.a_id)

编辑，替代方案：

SELECT * 
FROM A,B,
(SELECT c.b_id, c.a_id
    FROM a,c
    WHERE a.author = "Douglas Adams"
    AND a.id= c.a_id) X
WHERE b.id = x.b_id
    AND a.id = x.a_id