I have a database for a library system that records book loans. There are 2 tables, like this...
loanRecord - loanID, copyID
copyRecord - titleID, copyID, Title
Each book title may contain 1 or more copies. I am trying to create totals for the number of loans for each book title.
So far I have managed to create a query that successfully finds the copyID, titleID, Title and number of times borrowed for each borrowed copy...
Code: Select all
$queryID = mysql_query("SELECT COUNT(loanRecord.CopyID), loanRecord.CopyID, ID, Title
FROM loanRecord
LEFT JOIN copyRecord ON loanRecord.CopyID = copyRecord.CopyID
GROUP BY loanRecord.CopyID
ORDER BY ID,CopyID");
Code: Select all
CopyID ID Title Times borrowed
000114 6 Title A 1
000287 15 Title B 2
000222 23 Title C 7
000223 23 Title C 5
000274 56 Title D 1
000275 56 Title D 2
000276 56 Title D 2
000277 56 Title D 1
Cheers!