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.
sql join
asked Nov 10 '18 at 13:25
Achala Yasas PiyarathnaAchala Yasas Piyarathna
160112
add a comment |
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 '18 at 13:25
Achala Yasas PiyarathnaAchala Yasas Piyarathna
160112
3
Great. Do you have a question?
– Gordon Linoff
Nov 10 '18 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 '18 at 13:29
@GordonLinoff my question is what will be the sql query to retrieve the top 5 books
– Achala Yasas Piyarathna
Nov 10 '18 at 14:27
add a comment |
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 '18 at 13:25
Achala Yasas PiyarathnaAchala 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 '18 at 13:25
Achala Yasas PiyarathnaAchala Yasas Piyarathna
160112
asked Nov 10 '18 at 13:25
Achala Yasas PiyarathnaAchala Yasas Piyarathna
160112
asked Nov 10 '18 at 13:25
Achala Yasas PiyarathnaAchala Yasas Piyarathna
160112
asked Nov 10 '18 at 13:25
Achala Yasas PiyarathnaAchala Yasas Piyarathna
160112
asked Nov 10 '18 at 13:25
Achala Yasas PiyarathnaAchala Yasas Piyarathna
160112
160112
3
Great. Do you have a question?
– Gordon Linoff
Nov 10 '18 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 '18 at 13:29
@GordonLinoff my question is what will be the sql query to retrieve the top 5 books
– Achala Yasas Piyarathna
Nov 10 '18 at 14:27
add a comment |
3
Great. Do you have a question?
– Gordon Linoff
Nov 10 '18 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 '18 at 13:29
@GordonLinoff my question is what will be the sql query to retrieve the top 5 books
– Achala Yasas Piyarathna
Nov 10 '18 at 14:27
3
3
Great. Do you have a question?
– Gordon Linoff
Nov 10 '18 at 13:28
Great. Do you have a question?
– Gordon Linoff
Nov 10 '18 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 '18 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 '18 at 13:29
@GordonLinoff my question is what will be the sql query to retrieve the top 5 books
– Achala Yasas Piyarathna
Nov 10 '18 at 14:27
@GordonLinoff my question is what will be the sql query to retrieve the top 5 books
– Achala Yasas Piyarathna
Nov 10 '18 at 14:27
add a comment |
1 Answer
1
active
oldest
votes
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 '18 at 15:14
Antonio AlvarezAntonio Alvarez
147115
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
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
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 '18 at 15:14
Antonio AlvarezAntonio Alvarez
147115
add a comment |
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 '18 at 15:14
Antonio AlvarezAntonio Alvarez
147115
add a comment |
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 '18 at 15:14
Antonio AlvarezAntonio Alvarez
147115
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 '18 at 15:14
Antonio AlvarezAntonio Alvarez
147115
answered Nov 10 '18 at 15:14
Antonio AlvarezAntonio Alvarez
147115
answered Nov 10 '18 at 15:14
Antonio AlvarezAntonio Alvarez
147115
answered Nov 10 '18 at 15:14
Antonio AlvarezAntonio Alvarez
147115
147115
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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 '18 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 '18 at 13:29
@GordonLinoff my question is what will be the sql query to retrieve the top 5 books
– Achala Yasas Piyarathna
Nov 10 '18 at 14:27