Question 284715 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Here is my database: Is there any way to extract a list of the amount of times the same book has been borrowed alongside the title_name?

image of database

I have attempted to use inner joins but not sure which would connect where? Do I need to add another FK somewhere?

SELECT user_name, fkbook_id
FROM users
INNER JOIN loans        
ON
user_id = fkuser_id
ORDER BY fkbook_id;

i’ve managed to extract the list of all the book_id’s borrowed including any duplicate copies, but not sure how to get the title_name or if this is even possible? Any help/guidance is appreciated

Tags:

2

Answers


  1. 0

    Follow your diagram – you’ll need to JOIN through from loans -> books -> title:

    SELECT users.user_name, loans.fkbook_id, title.title_name
FROM users
INNER JOIN loans ON users.user_id = loans.fkuser_id
INNER JOIN books ON loans.fkbook_id = books.book_id
INNER JOIN title ON books.fktitle_id = title.title_id
ORDER BY fkbook_id;
    Login or Signup to reply.
  2. 0 
    SELECT b.book_id, t.title_name, COUNT(l.loan_id) As TimesBorrowed
FROM loans l
INNER JOIN books b ON b.book_id = l.fkbook_id
INNER JOIN title t on t.title_id = b.fktitle_id
GROUP BY b.book_id, t.title_name

    Odd to use varchar fields for the IDs. You’d typically want those to be numeric incrementing values, and if you’re concerned the database might actually get big enough to overflow a multi-billion item integer you use a guid or larger int type. But NOT varchar.

    Also odd to use int values for the dates, though MySQL has history here around unix timestamps.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search