如何为这5个表编写SQL查询？

I am writing an application that helps book exchange between users. I am using PHP and MySQL, and I am pretty new to them both.

我正在编写一个帮助预订用户之间交流的应用程序。我正在使用PHP和MySQL，我对他们两个都很陌生。

I have 5 tables, 3 data tables and 2 service tables:

我有5个表，3个数据表和2个服务表：

user: with user attributes (user_id, name, birth... etc).

user：具有用户属性（user_id，name，birth ...等）。
book: with book attributes (book_id, name, author, publisher... etc).

书：具有书籍属性（book_id，名称，作者，出版商等）。
copy: represents actual copies of a books (copy_id, condition, comments... etc).

copy：表示书籍的实际副本（copy_id，条件，注释等）。
user_copy: describes which user holds which copy, composed out of userID and copyID.

user_copy：描述哪个用户拥有由userID和copyID组成的副本。
copy_book: represents the connection of copy and book, composed out of copyID and bookID

copy_book：表示copy和book的连接，由copyID和bookID组成

My question is: what is the easiest and most efficient statement for getting the book attributes and copy attributes for each copy that a user holds?

我的问题是：获取用户拥有的每个副本的书籍属性和复制属性的最简单，最有效的语句是什么？

1 个解决方案

#1

You need to inner join all the tables that you are interested in: book, copy, user_copy, and copy_book. The SELECT statement that returns attributes on all copies held by a user may look like this:

您需要内部连接您感兴趣的所有表：book，copy，user_copy和copy_book。在用户持有的所有副本上返回属性的SELECT语句可能如下所示：

SELECT   B.bookID
       , B.name
       , B.author
       , B.publisher
       , C.condition
       , C.comments
       -- you may get other fields that you are interested in here..
  FROM book B
       INNER JOIN copy_book CB ON B.bookID = CB.bookID
       INNER JOIN user_copy UC ON UC.copyID = CB.copyID
       INNER JOIN copy C ON C.copyID = UC.copyID
  WHERE UC.userID = <the user Id that you want>

I hope it's pretty clear what the statement does but if you have any questions, please ask.

我希望陈述的内容很清楚，但如果您有任何疑问，请询问。

#1