CODEWITHSUNDEEP
sql
vin.g
vin.g

Reputation: 19

SQL Query involving two different tables

I need to get a result from two separate tables but I also want to use the count function.

Current query:

SELECT BOOK_NUM, COUNT(BOOK_NUM) 
FROM CHECKOUT 
GROUP BY BOOK_NUM        
HAVING COUNT(BOOK_NUM) > 7

Result:

BOOK_NUM    COUNT(BOOK_NUM))
----------------------------
5236            12
5235             9

I just also need to display the book title with this result set from the same book table.

Upvotes: 0

Views: 54

Answers (1)

Dale
Dale

Reputation: 1941

Disclaimer: Mostly guessing due to lack of information in question

You will need to JOIN to whatever table contains the book information I'll assume this is called BOOK and that it has two columns BOOK_NUM and NAME. We can use the BOOK_NUM column on the BOOK table to link to the BOOK_NUM column in the CHECKOUT table, these columns do not need to have the same name. All the non aggregate columns you include in the SELECT, in this case BOOK_NUM and BOOK.NAME will have to be in the GROUP BY clause.

SELECT CHECKOUT.BOOK_NUM, BOOK.NAME, COUNT(CHECKOUT.BOOK_NUM) 
FROM CHECKOUT 
JOIN BOOK ON BOOK.BOOK_NUM = CHECKOUT.BOOK_NUM
GROUP BY BOOK_NUM, BOOK.NAME      
HAVING COUNT(CHECKOUT.BOOK_NUM) >7

Upvotes: 2

Related Questions