首页 文章

从多对多关系中获取数据

提问于
浏览 次
2

我想从书表中获取 Headers ，并从主题表中获取某个作者的所有书籍的主题名称

这是表关系

enter image description here

enter image description here

我试图从表中选择 Headers 和主题，但我不能让它与多对多关系一起工作

SELECT book_title,subject_name FROM book,subject WHERE $subject_ID = subject_ID INNER JOIN book_author ON author_ID = '$author_ID'

我想做两个单独的查询，如果有人可以帮助我，我会很高兴 .

mysql database many-to-many

1 回答

  • 5

    试试这个：

    SELECT book_title, subject_name
FROM Book
INNER JOIN Book_Author ON Book.book_ISBN = Book_Author.book_ISBN
INNER JOIN Author ON Book_Author.author_ID = Author.author_ID
INNER JOIN Subject ON Subject.subject_ID = Book.subject_ID
WHERE author_lastname = [whatever];

    发布模特的好工作:)

    编辑以匹配确切的需求：

    SELECT book_title, subject_name
FROM Book
INNER JOIN Book_Author ON Book.book_ISBN = Book_Author.book_ISBN
INNER JOIN Subject ON Subject.subject_ID = Book.subject_ID
WHERE author_ID = '11';

    顺便说一下，你有一个 "Column 'author_ID' in where clause is ambiguous" 因为这一列出现在Book_Author和Author中 . 这就是为什么你必须用表名前缀:)

    回复于

相关问题