How to join complex sql queries within a table using subqueries
up vote
0
down vote
favorite
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.
sql join
asked Nov 10 at 13:25
Achala Yasas Piyarathna
160112
add a comment |
up vote
0
down vote
favorite
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.
sql join
asked Nov 10 at 13:25
Achala Yasas Piyarathna
160112
3
Great. Do you have a question?
– Gordon Linoff
Nov 10 at 13:28
Just as you did above, then access the properties with book.bookid, you can also declare multiple properties e.g. book(bookId int, name char(max))
– Jay
Nov 10 at 13:29
@GordonLinoff my question is what will be the sql query to retrieve the top 5 books
– Achala Yasas Piyarathna
Nov 10 at 14:27
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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.
sql join
asked Nov 10 at 13:25
Achala Yasas Piyarathna
160112
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.
sql join
sql join
asked Nov 10 at 13:25
Achala Yasas Piyarathna
160112
asked Nov 10 at 13:25
Achala Yasas Piyarathna
160112
asked Nov 10 at 13:25
Achala Yasas Piyarathna
160112
asked Nov 10 at 13:25
Achala Yasas Piyarathna
160112
asked Nov 10 at 13:25
Achala Yasas Piyarathna
160112
160112
3
Great. Do you have a question?
– Gordon Linoff
Nov 10 at 13:28
Just as you did above, then access the properties with book.bookid, you can also declare multiple properties e.g. book(bookId int, name char(max))
– Jay
Nov 10 at 13:29
@GordonLinoff my question is what will be the sql query to retrieve the top 5 books
– Achala Yasas Piyarathna
Nov 10 at 14:27
add a comment |
3
Great. Do you have a question?
– Gordon Linoff
Nov 10 at 13:28
Just as you did above, then access the properties with book.bookid, you can also declare multiple properties e.g. book(bookId int, name char(max))
– Jay
Nov 10 at 13:29
@GordonLinoff my question is what will be the sql query to retrieve the top 5 books
– Achala Yasas Piyarathna
Nov 10 at 14:27
3
3
Great. Do you have a question?
– Gordon Linoff
Nov 10 at 13:28
Great. Do you have a question?
– Gordon Linoff
Nov 10 at 13:28
Just as you did above, then access the properties with book.bookid, you can also declare multiple properties e.g. book(bookId int, name char(max))
– Jay
Nov 10 at 13:29
Just as you did above, then access the properties with book.bookid, you can also declare multiple properties e.g. book(bookId int, name char(max))
– Jay
Nov 10 at 13:29
@GordonLinoff my question is what will be the sql query to retrieve the top 5 books
– Achala Yasas Piyarathna
Nov 10 at 14:27
@GordonLinoff my question is what will be the sql query to retrieve the top 5 books
– Achala Yasas Piyarathna
Nov 10 at 14:27
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
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;
answered Nov 10 at 15:14
Antonio Alvarez
10214
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
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;
answered Nov 10 at 15:14
Antonio Alvarez
10214
add a comment |
up vote
1
down vote
accepted
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;
answered Nov 10 at 15:14
Antonio Alvarez
10214
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
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;
answered Nov 10 at 15:14
Antonio Alvarez
10214
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;
answered Nov 10 at 15:14
Antonio Alvarez
10214
answered Nov 10 at 15:14
Antonio Alvarez
10214
answered Nov 10 at 15:14
Antonio Alvarez
10214
answered Nov 10 at 15:14
Antonio Alvarez
10214
10214
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53239393%2fhow-to-join-complex-sql-queries-within-a-table-using-subqueries%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
Great. Do you have a question?
– Gordon Linoff
Nov 10 at 13:28
Just as you did above, then access the properties with book.bookid, you can also declare multiple properties e.g. book(bookId int, name char(max))
– Jay
Nov 10 at 13:29
@GordonLinoff my question is what will be the sql query to retrieve the top 5 books
– Achala Yasas Piyarathna
Nov 10 at 14:27