I'm writing a credit card applicatoin that can help book exchange between customers. I'm using PHP and MySQL, and i'm pretty a new comer to both of them.
I've 5 tables, 3 data tables and a pair of service tables:
user: with user characteristics (user_id, title, birth... etc).
book: with book characteristics (book_id, title, author, writer... etc).
copy: signifies actual copies of the books (copy_id, condition, comments... etc).
user_copy: describes which user holds which copy, composed from userID and copyID.
copy_book: signifies the bond of copy and book, composed from copyID and bookID
My real question is: what's the simplest and many efficient statement to get the book characteristics and copy characteristics for every copy that the user holds?
You have to inner join all of the tables that you are looking at: book, copy, user_copy, and copy_book. The Choose statement that returns characteristics on all copies held with a user may seem 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 really hope it's pretty obvious exactly what the statement does but when you've any queries, please request.