CODEWITHSUNDEEP
mysqlsqlcomparison
assignment_operator
assignment_operator

Reputation: 1365

MySQL - accessing a table sum and compare to another table?

This is for a homework assignment. I just plain don't understand how to do it.

The instructions for this particular question is:

List the branch name for all branches that have at least one
book that has at least 4 copies on hand.

Where the tables in question are:

Branch: 

BranchName        | BranchId
Henry Downtown    |    1
16 Riverview      |    2
Henry On The Hill |    3

Inventory:

BookId   |  BranchId  |  OnHand
   1     |     1      |     2
   2     |     3      |     4
   3     |     1      |     8
   4     |     3      |     1
   5     |     1      |     2
   6     |     2      |     3

From what I understand, I can get the number of OnHand per branch name with: 

SELECT BranchName, SUM(OnHand) 
FROM Branch B, Inventory I 
WHERE B.BranchId = I.BranchId 
GROUP BY BranchName;

but I don't get how I'd do the comparison between the sum of OnHand per branch and 4.

Any help would be appreciated, guys!

Upvotes: 0

Views: 246

Answers (1)

PM 77-1
PM 77-1

Reputation: 13334

Something like this (assuming that the same book could be in different branches):

SELECT DISTINCT BranchName 
FROM Branch B, Inventory I 
WHERE B.BranchId = I.BranchId 
GROUP BY BranchName, I.BookId
HAVING SUM(onHand) >= 4;

SQL Fiddle

Is that what you had in mind?

EDIT

If Inventory table lists each book only once for each branch, then we can get rid of grouping by bookid and DISTINCT clause.

The query becomes:

SELECT BranchName 
FROM Branch B, Inventory I 
WHERE B.BranchId = I.BranchId 
GROUP BY BranchName
HAVING SUM(onHand) >= 4;

This SQL Fiddle is for the simplified query.

Upvotes: 1

Related Questions