user3258494 user3258494 - 1 year ago 86
MySQL Question

multiple joins & multiplication across

I asked a question earlier regarding joins and now I'm stuck once again.

Here is what the db looks like:

Book



Book_code (unique)
Price
book_title
publisher_code
book_type


Branch



Branch_num (unique)
Branch_name (unique)
branch_location
num_employees


Inventory



Branch_number
Book_code
On_hand ( the quantity of books )


now the question is: List branches that have the total inventory amount (i.e., sum of on_hand times price) greater than 300.

I know I have to make use of all the tables. I have started out coding the following:

SELECT br.branch_name, br.branch_num, i.on_hand , b.book_code, b.price
FROM book b, inventory i, branch br
WHERE br.branch_num = i.branch_num
and b.book_code = i.book_code;


but im stuck after that.

Answer Source
SELECT br.branch_name, br.branch_num, SUM(i.on_hand * b.price) as on_hand_price
FROM book b, inventory i, branch br
WHERE br.branch_num = i.branch_num
and b.book_code = i.book_code
GROUP by br.branch_num,br.branch_name
Having Sum(i.on_hand* b.price) > 300
ORDER BY branch_name;

You just missed a GROUPing over branch number and name.. and finally verifying the SUM of on hand amount check in that group using hAVING.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download