Clever Geek Handbook

How do I write a SQL query for these 5 tables?

I am writing an application that helps exchange books between users. I am using PHP and MySQL and I am fairly new to both of them.

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

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

  • book: with book attributes (book_id, name, author, publisher ... etc.).

  • copy: represents actual copies of books (copy_id, condition, comments ... etc).

  • user_copy: Describes which user is holding a copy made up of user ID and copyID.

  • copy_book: represents a mix of a copy and a book made up of copyID and bookID

My question is, what is the simplest and most efficient way to get the book attributes and copy attributes for each copy that the user holds?

+2


source to share


1 answer


You need to concatenate all tables you are interested in: book, copy, user_copy and copy_book. A SELECT statement that returns attributes for all copies held by the user might look like this:

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 expression does, but if you have any questions, ask.

+6


source






More articles:


All Articles