• 0

[SQL] Help With A Complex Query


Question

I have four tables book,bookcopy,bookloan and customer. Book.book_id = Bookcopy.book_id and bookcopy.copy_id = bookloan.copy_id this is how the tables are joined together ignore the customer table for now.

I have to find the copies of books that have their number of hirings below the average hirings for copies of that book.

Imagine three copies of a particular book, one each in town A,B and C

Copy at A is hired 3 times

Copy at B is hired 4 times

Copy at C is hired 5 times

Average hiring per copy is 4

Copy C has above average hirings (for that particular book).

Is a good way to look at it.

I have been on this for the last 6 hours and have got the following SQL

SELECT book.book_id, NVL(location,'No Copies Held') AS Location, COUNT(bookcopy.copy_id)  AS Number_Of_Copies
FROM book LEFT OUTER JOIN bookcopy
ON book.book_id = bookcopy.book_id
GROUP BY book.book_id,location
ORDER by book.book_id;

This just outputs the number of copies held for each book.

Does anyone have any idea at all on where to do? I am tearing my hair out over this.

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0

try this:

SELECT b.*, c.*, tmp1.num_copies, tmp1.num_book_loans, tmp1.avg_copy_loans, COUNT(l.copy_id) as num_copy_loans
FROM book b, (
	SELECT b2.book_id, COUNT(*) as num_copies, SUM(tmp2.num_copy_loans) as num_book_loans, AVG(tmp2.num_copy_loans) as avg_copy_loans
	FROM book b2, (
		SELECT c3.book_id, COUNT(l3.copy_id) as num_copy_loans
		FROM bookcopy c3
		LEFT JOIN bookloan l3 ON c3.copy_id=l3.copy_id
		GROUP BY c3.copy_id
	) tmp2
	WHERE b2.book_id=tmp2.book_id
	GROUP BY b2.book_id
) tmp1
INNER JOIN bookcopy c ON b.book_id=c.book_id
LEFT JOIN bookloan l ON c.copy_id=l.copy_id
WHERE (b.book_id=tmp1.book_id)
GROUP BY c.copy_id
HAVING COUNT(l.copy_id)<tmp1.avg_copy_loans
ORDER by b.book_id

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.