CODEWITHSUNDEEP
sqljoin
Achala Yasas Piyarathna
Achala Yasas Piyarathna

Reputation: 148

How to join complex sql queries within a table using subqueries

I have a table with "userid", "bookid", "sessionid".

When a book(bookid) is viewed by a visitor(userid), the web application will find all books(bookids) viewed by users(userids) who viewed this book(bookid) and display the top 5 books.

Upvotes: 0

Views: 42

Answers (1)

Antonio Alvarez
Antonio Alvarez

Reputation: 476

I will assume the following:

  • You are using MySQL as DBMS.
  • With "Top 5 books" you mean "the 5 most viewed books considering the views by the users who viewed the current book".

Assuming as an example that the user is visiting the book 151 at the moment, you can do it with the following query:

SELECT bookid, COUNT(*) AS BookViews
FROM YourTable
WHERE userid IN (SELECT userid FROM YourTable WHERE bookid = 151)
GROUP BY bookid
ORDER BY 2 DESC
LIMIT 5;

Upvotes: 1

Related Questions