Is subquery most efficient in this way?
Here is my code, I am trying to get the total sum of prices of books which appear in the order made by 'Village bookshop' on the '27/09/2018'
Relevant tables
book(isbn, title, authorID, genre, pubYear, publisher, rrPrice, avgRating)
bookShop(shopNo, shopName, street, city, county)
orders(orderNo, sNo, oDate, salesRep)
orderDetails(oNo, bookISBN, quantity)
My question is, is there a more efficient way than a subquery to do this?
SELECT sum(rrPrice)
FROM book
WHERE isbn in(
SELECT bookISBN
FROM orderDetails INNER JOIN orders ON orderNO = oNO
INNER JOIN bookShop on sNO = shopNo
WHERE shopName = 'Village bookshop' and oDate='27/09/2018'
)
mysql join subquery
edited Nov 12 '18 at 16:13
Tomalak
257k51426542
asked Nov 12 '18 at 15:59
JadeJade
145
|
show 1 more comment
Here is my code, I am trying to get the total sum of prices of books which appear in the order made by 'Village bookshop' on the '27/09/2018'
Relevant tables
book(isbn, title, authorID, genre, pubYear, publisher, rrPrice, avgRating)
bookShop(shopNo, shopName, street, city, county)
orders(orderNo, sNo, oDate, salesRep)
orderDetails(oNo, bookISBN, quantity)
My question is, is there a more efficient way than a subquery to do this?
SELECT sum(rrPrice)
FROM book
WHERE isbn in(
SELECT bookISBN
FROM orderDetails INNER JOIN orders ON orderNO = oNO
INNER JOIN bookShop on sNO = shopNo
WHERE shopName = 'Village bookshop' and oDate='27/09/2018'
)
mysql join subquery
edited Nov 12 '18 at 16:13
Tomalak
257k51426542
asked Nov 12 '18 at 15:59
JadeJade
145
Yes, possiblyJOINbased approach.
– Madhur Bhaiya
Nov 12 '18 at 16:10
3
Sorry, I have a question unrelated to your question. What is the reason toSUM(rrprice)? It makes no sense to me.
– Alex
Nov 12 '18 at 16:14
@Alex adding the prices of the books which fit the query
– Jade
Nov 12 '18 at 16:18
2
@Jade and? what that information brings or explains to anybody? if sum of prices equal 1000$? or 5000$? usually people do care about revenue so it should beSUM(rrprice*quantity)but without quantity - that is useless info.
– Alex
Nov 12 '18 at 16:20
1
Note also that as well as having multiple ISBNs, books can be by multiple authors, and dates are generally best stored as dates.
– Strawberry
Nov 12 '18 at 16:37
|
show 1 more comment
Here is my code, I am trying to get the total sum of prices of books which appear in the order made by 'Village bookshop' on the '27/09/2018'
Relevant tables
book(isbn, title, authorID, genre, pubYear, publisher, rrPrice, avgRating)
bookShop(shopNo, shopName, street, city, county)
orders(orderNo, sNo, oDate, salesRep)
orderDetails(oNo, bookISBN, quantity)
My question is, is there a more efficient way than a subquery to do this?
SELECT sum(rrPrice)
FROM book
WHERE isbn in(
SELECT bookISBN
FROM orderDetails INNER JOIN orders ON orderNO = oNO
INNER JOIN bookShop on sNO = shopNo
WHERE shopName = 'Village bookshop' and oDate='27/09/2018'
)
mysql join subquery
edited Nov 12 '18 at 16:13
Tomalak
257k51426542
asked Nov 12 '18 at 15:59
JadeJade
145
Here is my code, I am trying to get the total sum of prices of books which appear in the order made by 'Village bookshop' on the '27/09/2018'
Relevant tables
book(isbn, title, authorID, genre, pubYear, publisher, rrPrice, avgRating)
bookShop(shopNo, shopName, street, city, county)
orders(orderNo, sNo, oDate, salesRep)
orderDetails(oNo, bookISBN, quantity)
My question is, is there a more efficient way than a subquery to do this?
SELECT sum(rrPrice)
FROM book
WHERE isbn in(
SELECT bookISBN
FROM orderDetails INNER JOIN orders ON orderNO = oNO
INNER JOIN bookShop on sNO = shopNo
WHERE shopName = 'Village bookshop' and oDate='27/09/2018'
)
mysql join subquery
mysql join subquery
edited Nov 12 '18 at 16:13
Tomalak
257k51426542
asked Nov 12 '18 at 15:59
JadeJade
145
edited Nov 12 '18 at 16:13
Tomalak
257k51426542
asked Nov 12 '18 at 15:59
JadeJade
145
edited Nov 12 '18 at 16:13
Tomalak
257k51426542
edited Nov 12 '18 at 16:13
Tomalak
257k51426542
edited Nov 12 '18 at 16:13
Tomalak
257k51426542
257k51426542
asked Nov 12 '18 at 15:59
JadeJade
145
asked Nov 12 '18 at 15:59
JadeJade
145
asked Nov 12 '18 at 15:59
JadeJade
145
145
Yes, possiblyJOINbased approach.
– Madhur Bhaiya
Nov 12 '18 at 16:10
3
Sorry, I have a question unrelated to your question. What is the reason toSUM(rrprice)? It makes no sense to me.
– Alex
Nov 12 '18 at 16:14
@Alex adding the prices of the books which fit the query
– Jade
Nov 12 '18 at 16:18
2
@Jade and? what that information brings or explains to anybody? if sum of prices equal 1000$? or 5000$? usually people do care about revenue so it should beSUM(rrprice*quantity)but without quantity - that is useless info.
– Alex
Nov 12 '18 at 16:20
1
Note also that as well as having multiple ISBNs, books can be by multiple authors, and dates are generally best stored as dates.
– Strawberry
Nov 12 '18 at 16:37
|
show 1 more comment
Yes, possiblyJOINbased approach.
– Madhur Bhaiya
Nov 12 '18 at 16:10
3
Sorry, I have a question unrelated to your question. What is the reason toSUM(rrprice)? It makes no sense to me.
– Alex
Nov 12 '18 at 16:14
@Alex adding the prices of the books which fit the query
– Jade
Nov 12 '18 at 16:18
2
@Jade and? what that information brings or explains to anybody? if sum of prices equal 1000$? or 5000$? usually people do care about revenue so it should beSUM(rrprice*quantity)but without quantity - that is useless info.
– Alex
Nov 12 '18 at 16:20
1
Note also that as well as having multiple ISBNs, books can be by multiple authors, and dates are generally best stored as dates.
– Strawberry
Nov 12 '18 at 16:37
Yes, possibly
JOIN based approach.– Madhur Bhaiya
Nov 12 '18 at 16:10
Yes, possibly
JOIN based approach.– Madhur Bhaiya
Nov 12 '18 at 16:10
3
3
Sorry, I have a question unrelated to your question. What is the reason to
SUM(rrprice) ? It makes no sense to me.– Alex
Nov 12 '18 at 16:14
Sorry, I have a question unrelated to your question. What is the reason to
SUM(rrprice) ? It makes no sense to me.– Alex
Nov 12 '18 at 16:14
@Alex adding the prices of the books which fit the query
– Jade
Nov 12 '18 at 16:18
@Alex adding the prices of the books which fit the query
– Jade
Nov 12 '18 at 16:18
2
2
@Jade and? what that information brings or explains to anybody? if sum of prices equal 1000$? or 5000$? usually people do care about revenue so it should be
SUM(rrprice*quantity) but without quantity - that is useless info.– Alex
Nov 12 '18 at 16:20
@Jade and? what that information brings or explains to anybody? if sum of prices equal 1000$? or 5000$? usually people do care about revenue so it should be
SUM(rrprice*quantity) but without quantity - that is useless info.– Alex
Nov 12 '18 at 16:20
1
1
Note also that as well as having multiple ISBNs, books can be by multiple authors, and dates are generally best stored as dates.
– Strawberry
Nov 12 '18 at 16:37
Note also that as well as having multiple ISBNs, books can be by multiple authors, and dates are generally best stored as dates.
– Strawberry
Nov 12 '18 at 16:37
|
show 1 more comment
3 Answers
3
active
oldest
votes
There's a lot of alternatives for your query. I'd suggest using the EXISTS clause, since it only returns logical values which is always faster than comparing strings or numbers.
It will be something like:
SELECT sum(rrPrice)
FROM book
WHERE EXISTS (
SELECT *
FROM orderDetails od INNER JOIN orders ON (orderNO = oNO)
INNER JOIN bookShop ON (sNO = shopNo)
WHERE shopName = 'Village bookshop' AND oDate='27/09/2018' AND book.isbn=od.bookISBN
)
It's just a matter of readability and prolixity, since you're not going to get away from the IJ. You can also do some IJ without doing any subquery and it'll be right as well.
answered Nov 12 '18 at 16:18
J. AlmandosJ. Almandos
1978
add a comment |
Normally a inner join on a subquery work better that an IN clause on the same subquery so try using
SELECT sum(rrPrice)
FROM book
INNER JOIN (
SELECT bookISBN
FROM orderDetails
INNER JOIN orders ON orderNO = oNO
INNER JOIN bookShop on sNO = shopNo
WHERE shopName = 'Village bookshop' and oDate='27/09/2018'
) t on book.isbn = t.bookISBN
The query is only related to the question is there is a most efficent way for do what you do with the IN clause .. and don't eval aspect related to you data content
answered Nov 12 '18 at 16:19
scaisEdgescaisEdge
91.8k104870
1
your last query will bring different result vs original one. Original one takesbook.rrpriceone time perbook.isbn. But your queryINNER JOIN orderDetailswhich means we can and will have many records with sameorderDetails.bookISBN = book.isbnsoSUMwill multiply randomly. Hmm... and seem 1st query has the same issue...
– Alex
Nov 12 '18 at 16:29
This unjustified "normally" is entirely dependent on unstated assumptions about particular implementations of particular DBMSs. PS Now there is no IN, your post is not clear.
– philipxy
Nov 12 '18 at 21:00
add a comment |
EDIT: After reading, this is NOT the best approach to use. Leaving this up for others to learn from.
You could select from multiple tables and handle more logic in your "WHERE" clause. Something similar to this:
SELECT sum(rrPrice)
FROM book, bookShop, orders, orderDetails
WHERE book.isbn = orderDetails.bookISBN AND orderDetails.oNo = orders.orderNo AND bookShop.shopNo = orders.sNo and shopName = 'Village bookshop' and oDate='27/09/2018'
answered Nov 12 '18 at 16:12
TiddlyWiddlyTiddlyWiddly
54
2
It is bad practice to put join conditions in awhereclause.
– trincot
Nov 12 '18 at 16:13
Please don't use Old comma based Implicit joins and use Modern ExplicitJoinbased syntax
– Madhur Bhaiya
Nov 12 '18 at 16:14
Did not know as I am relatively new to MySQL. I'll read into it. Thanks
– TiddlyWiddly
Nov 12 '18 at 16:16
2
Leaving this up for others to learn from - Sorry but people should learn from best examples and best practices but not from random wrong ones.
– Alex
Nov 12 '18 at 16:22
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%2f53265821%2fis-subquery-most-efficient-in-this-way%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
There's a lot of alternatives for your query. I'd suggest using the EXISTS clause, since it only returns logical values which is always faster than comparing strings or numbers.
It will be something like:
SELECT sum(rrPrice)
FROM book
WHERE EXISTS (
SELECT *
FROM orderDetails od INNER JOIN orders ON (orderNO = oNO)
INNER JOIN bookShop ON (sNO = shopNo)
WHERE shopName = 'Village bookshop' AND oDate='27/09/2018' AND book.isbn=od.bookISBN
)
It's just a matter of readability and prolixity, since you're not going to get away from the IJ. You can also do some IJ without doing any subquery and it'll be right as well.
answered Nov 12 '18 at 16:18
J. AlmandosJ. Almandos
1978
add a comment |
There's a lot of alternatives for your query. I'd suggest using the EXISTS clause, since it only returns logical values which is always faster than comparing strings or numbers.
It will be something like:
SELECT sum(rrPrice)
FROM book
WHERE EXISTS (
SELECT *
FROM orderDetails od INNER JOIN orders ON (orderNO = oNO)
INNER JOIN bookShop ON (sNO = shopNo)
WHERE shopName = 'Village bookshop' AND oDate='27/09/2018' AND book.isbn=od.bookISBN
)
It's just a matter of readability and prolixity, since you're not going to get away from the IJ. You can also do some IJ without doing any subquery and it'll be right as well.
answered Nov 12 '18 at 16:18
J. AlmandosJ. Almandos
1978
add a comment |
There's a lot of alternatives for your query. I'd suggest using the EXISTS clause, since it only returns logical values which is always faster than comparing strings or numbers.
It will be something like:
SELECT sum(rrPrice)
FROM book
WHERE EXISTS (
SELECT *
FROM orderDetails od INNER JOIN orders ON (orderNO = oNO)
INNER JOIN bookShop ON (sNO = shopNo)
WHERE shopName = 'Village bookshop' AND oDate='27/09/2018' AND book.isbn=od.bookISBN
)
It's just a matter of readability and prolixity, since you're not going to get away from the IJ. You can also do some IJ without doing any subquery and it'll be right as well.
answered Nov 12 '18 at 16:18
J. AlmandosJ. Almandos
1978
There's a lot of alternatives for your query. I'd suggest using the EXISTS clause, since it only returns logical values which is always faster than comparing strings or numbers.
It will be something like:
SELECT sum(rrPrice)
FROM book
WHERE EXISTS (
SELECT *
FROM orderDetails od INNER JOIN orders ON (orderNO = oNO)
INNER JOIN bookShop ON (sNO = shopNo)
WHERE shopName = 'Village bookshop' AND oDate='27/09/2018' AND book.isbn=od.bookISBN
)
It's just a matter of readability and prolixity, since you're not going to get away from the IJ. You can also do some IJ without doing any subquery and it'll be right as well.
answered Nov 12 '18 at 16:18
J. AlmandosJ. Almandos
1978
answered Nov 12 '18 at 16:18
J. AlmandosJ. Almandos
1978
answered Nov 12 '18 at 16:18
J. AlmandosJ. Almandos
1978
answered Nov 12 '18 at 16:18
J. AlmandosJ. Almandos
1978
1978
add a comment |
add a comment |
Normally a inner join on a subquery work better that an IN clause on the same subquery so try using
SELECT sum(rrPrice)
FROM book
INNER JOIN (
SELECT bookISBN
FROM orderDetails
INNER JOIN orders ON orderNO = oNO
INNER JOIN bookShop on sNO = shopNo
WHERE shopName = 'Village bookshop' and oDate='27/09/2018'
) t on book.isbn = t.bookISBN
The query is only related to the question is there is a most efficent way for do what you do with the IN clause .. and don't eval aspect related to you data content
answered Nov 12 '18 at 16:19
scaisEdgescaisEdge
91.8k104870
1
your last query will bring different result vs original one. Original one takesbook.rrpriceone time perbook.isbn. But your queryINNER JOIN orderDetailswhich means we can and will have many records with sameorderDetails.bookISBN = book.isbnsoSUMwill multiply randomly. Hmm... and seem 1st query has the same issue...
– Alex
Nov 12 '18 at 16:29
This unjustified "normally" is entirely dependent on unstated assumptions about particular implementations of particular DBMSs. PS Now there is no IN, your post is not clear.
– philipxy
Nov 12 '18 at 21:00
add a comment |
Normally a inner join on a subquery work better that an IN clause on the same subquery so try using
SELECT sum(rrPrice)
FROM book
INNER JOIN (
SELECT bookISBN
FROM orderDetails
INNER JOIN orders ON orderNO = oNO
INNER JOIN bookShop on sNO = shopNo
WHERE shopName = 'Village bookshop' and oDate='27/09/2018'
) t on book.isbn = t.bookISBN
The query is only related to the question is there is a most efficent way for do what you do with the IN clause .. and don't eval aspect related to you data content
answered Nov 12 '18 at 16:19
scaisEdgescaisEdge
91.8k104870
1
your last query will bring different result vs original one. Original one takesbook.rrpriceone time perbook.isbn. But your queryINNER JOIN orderDetailswhich means we can and will have many records with sameorderDetails.bookISBN = book.isbnsoSUMwill multiply randomly. Hmm... and seem 1st query has the same issue...
– Alex
Nov 12 '18 at 16:29
This unjustified "normally" is entirely dependent on unstated assumptions about particular implementations of particular DBMSs. PS Now there is no IN, your post is not clear.
– philipxy
Nov 12 '18 at 21:00
add a comment |
Normally a inner join on a subquery work better that an IN clause on the same subquery so try using
SELECT sum(rrPrice)
FROM book
INNER JOIN (
SELECT bookISBN
FROM orderDetails
INNER JOIN orders ON orderNO = oNO
INNER JOIN bookShop on sNO = shopNo
WHERE shopName = 'Village bookshop' and oDate='27/09/2018'
) t on book.isbn = t.bookISBN
The query is only related to the question is there is a most efficent way for do what you do with the IN clause .. and don't eval aspect related to you data content
answered Nov 12 '18 at 16:19
scaisEdgescaisEdge
91.8k104870
Normally a inner join on a subquery work better that an IN clause on the same subquery so try using
SELECT sum(rrPrice)
FROM book
INNER JOIN (
SELECT bookISBN
FROM orderDetails
INNER JOIN orders ON orderNO = oNO
INNER JOIN bookShop on sNO = shopNo
WHERE shopName = 'Village bookshop' and oDate='27/09/2018'
) t on book.isbn = t.bookISBN
The query is only related to the question is there is a most efficent way for do what you do with the IN clause .. and don't eval aspect related to you data content
answered Nov 12 '18 at 16:19
scaisEdgescaisEdge
91.8k104870
edited Nov 12 '18 at 16:47
answered Nov 12 '18 at 16:19
scaisEdgescaisEdge
91.8k104870
answered Nov 12 '18 at 16:19
scaisEdgescaisEdge
91.8k104870
answered Nov 12 '18 at 16:19
scaisEdgescaisEdge
91.8k104870
91.8k104870
1
your last query will bring different result vs original one. Original one takesbook.rrpriceone time perbook.isbn. But your queryINNER JOIN orderDetailswhich means we can and will have many records with sameorderDetails.bookISBN = book.isbnsoSUMwill multiply randomly. Hmm... and seem 1st query has the same issue...
– Alex
Nov 12 '18 at 16:29
This unjustified "normally" is entirely dependent on unstated assumptions about particular implementations of particular DBMSs. PS Now there is no IN, your post is not clear.
– philipxy
Nov 12 '18 at 21:00
add a comment |
1
your last query will bring different result vs original one. Original one takesbook.rrpriceone time perbook.isbn. But your queryINNER JOIN orderDetailswhich means we can and will have many records with sameorderDetails.bookISBN = book.isbnsoSUMwill multiply randomly. Hmm... and seem 1st query has the same issue...
– Alex
Nov 12 '18 at 16:29
This unjustified "normally" is entirely dependent on unstated assumptions about particular implementations of particular DBMSs. PS Now there is no IN, your post is not clear.
– philipxy
Nov 12 '18 at 21:00
1
1
your last query will bring different result vs original one. Original one takes
book.rrprice one time per book.isbn. But your query INNER JOIN orderDetails which means we can and will have many records with same orderDetails.bookISBN = book.isbn so SUM will multiply randomly. Hmm... and seem 1st query has the same issue...– Alex
Nov 12 '18 at 16:29
your last query will bring different result vs original one. Original one takes
book.rrprice one time per book.isbn. But your query INNER JOIN orderDetails which means we can and will have many records with same orderDetails.bookISBN = book.isbn so SUM will multiply randomly. Hmm... and seem 1st query has the same issue...– Alex
Nov 12 '18 at 16:29
This unjustified "normally" is entirely dependent on unstated assumptions about particular implementations of particular DBMSs. PS Now there is no IN, your post is not clear.
– philipxy
Nov 12 '18 at 21:00
This unjustified "normally" is entirely dependent on unstated assumptions about particular implementations of particular DBMSs. PS Now there is no IN, your post is not clear.
– philipxy
Nov 12 '18 at 21:00
add a comment |
EDIT: After reading, this is NOT the best approach to use. Leaving this up for others to learn from.
You could select from multiple tables and handle more logic in your "WHERE" clause. Something similar to this:
SELECT sum(rrPrice)
FROM book, bookShop, orders, orderDetails
WHERE book.isbn = orderDetails.bookISBN AND orderDetails.oNo = orders.orderNo AND bookShop.shopNo = orders.sNo and shopName = 'Village bookshop' and oDate='27/09/2018'
answered Nov 12 '18 at 16:12
TiddlyWiddlyTiddlyWiddly
54
2
It is bad practice to put join conditions in awhereclause.
– trincot
Nov 12 '18 at 16:13
Please don't use Old comma based Implicit joins and use Modern ExplicitJoinbased syntax
– Madhur Bhaiya
Nov 12 '18 at 16:14
Did not know as I am relatively new to MySQL. I'll read into it. Thanks
– TiddlyWiddly
Nov 12 '18 at 16:16
2
Leaving this up for others to learn from - Sorry but people should learn from best examples and best practices but not from random wrong ones.
– Alex
Nov 12 '18 at 16:22
add a comment |
EDIT: After reading, this is NOT the best approach to use. Leaving this up for others to learn from.
You could select from multiple tables and handle more logic in your "WHERE" clause. Something similar to this:
SELECT sum(rrPrice)
FROM book, bookShop, orders, orderDetails
WHERE book.isbn = orderDetails.bookISBN AND orderDetails.oNo = orders.orderNo AND bookShop.shopNo = orders.sNo and shopName = 'Village bookshop' and oDate='27/09/2018'
answered Nov 12 '18 at 16:12
TiddlyWiddlyTiddlyWiddly
54
2
It is bad practice to put join conditions in awhereclause.
– trincot
Nov 12 '18 at 16:13
Please don't use Old comma based Implicit joins and use Modern ExplicitJoinbased syntax
– Madhur Bhaiya
Nov 12 '18 at 16:14
Did not know as I am relatively new to MySQL. I'll read into it. Thanks
– TiddlyWiddly
Nov 12 '18 at 16:16
2
Leaving this up for others to learn from - Sorry but people should learn from best examples and best practices but not from random wrong ones.
– Alex
Nov 12 '18 at 16:22
add a comment |
EDIT: After reading, this is NOT the best approach to use. Leaving this up for others to learn from.
You could select from multiple tables and handle more logic in your "WHERE" clause. Something similar to this:
SELECT sum(rrPrice)
FROM book, bookShop, orders, orderDetails
WHERE book.isbn = orderDetails.bookISBN AND orderDetails.oNo = orders.orderNo AND bookShop.shopNo = orders.sNo and shopName = 'Village bookshop' and oDate='27/09/2018'
answered Nov 12 '18 at 16:12
TiddlyWiddlyTiddlyWiddly
54
EDIT: After reading, this is NOT the best approach to use. Leaving this up for others to learn from.
You could select from multiple tables and handle more logic in your "WHERE" clause. Something similar to this:
SELECT sum(rrPrice)
FROM book, bookShop, orders, orderDetails
WHERE book.isbn = orderDetails.bookISBN AND orderDetails.oNo = orders.orderNo AND bookShop.shopNo = orders.sNo and shopName = 'Village bookshop' and oDate='27/09/2018'
answered Nov 12 '18 at 16:12
TiddlyWiddlyTiddlyWiddly
54
edited Nov 12 '18 at 16:20
answered Nov 12 '18 at 16:12
TiddlyWiddlyTiddlyWiddly
54
answered Nov 12 '18 at 16:12
TiddlyWiddlyTiddlyWiddly
54
answered Nov 12 '18 at 16:12
TiddlyWiddlyTiddlyWiddly
54
54
2
It is bad practice to put join conditions in awhereclause.
– trincot
Nov 12 '18 at 16:13
Please don't use Old comma based Implicit joins and use Modern ExplicitJoinbased syntax
– Madhur Bhaiya
Nov 12 '18 at 16:14
Did not know as I am relatively new to MySQL. I'll read into it. Thanks
– TiddlyWiddly
Nov 12 '18 at 16:16
2
Leaving this up for others to learn from - Sorry but people should learn from best examples and best practices but not from random wrong ones.
– Alex
Nov 12 '18 at 16:22
add a comment |
2
It is bad practice to put join conditions in awhereclause.
– trincot
Nov 12 '18 at 16:13
Please don't use Old comma based Implicit joins and use Modern ExplicitJoinbased syntax
– Madhur Bhaiya
Nov 12 '18 at 16:14
Did not know as I am relatively new to MySQL. I'll read into it. Thanks
– TiddlyWiddly
Nov 12 '18 at 16:16
2
Leaving this up for others to learn from - Sorry but people should learn from best examples and best practices but not from random wrong ones.
– Alex
Nov 12 '18 at 16:22
2
2
It is bad practice to put join conditions in a
where clause.– trincot
Nov 12 '18 at 16:13
It is bad practice to put join conditions in a
where clause.– trincot
Nov 12 '18 at 16:13
Please don't use Old comma based Implicit joins and use Modern Explicit
Join based syntax– Madhur Bhaiya
Nov 12 '18 at 16:14
Please don't use Old comma based Implicit joins and use Modern Explicit
Join based syntax– Madhur Bhaiya
Nov 12 '18 at 16:14
Did not know as I am relatively new to MySQL. I'll read into it. Thanks
– TiddlyWiddly
Nov 12 '18 at 16:16
Did not know as I am relatively new to MySQL. I'll read into it. Thanks
– TiddlyWiddly
Nov 12 '18 at 16:16
2
2
Leaving this up for others to learn from - Sorry but people should learn from best examples and best practices but not from random wrong ones.
– Alex
Nov 12 '18 at 16:22
Leaving this up for others to learn from - Sorry but people should learn from best examples and best practices but not from random wrong ones.
– Alex
Nov 12 '18 at 16:22
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.
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%2f53265821%2fis-subquery-most-efficient-in-this-way%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
Yes, possibly
JOINbased approach.– Madhur Bhaiya
Nov 12 '18 at 16:10
3
Sorry, I have a question unrelated to your question. What is the reason to
SUM(rrprice)? It makes no sense to me.– Alex
Nov 12 '18 at 16:14
@Alex adding the prices of the books which fit the query
– Jade
Nov 12 '18 at 16:18
2
@Jade and? what that information brings or explains to anybody? if sum of prices equal 1000$? or 5000$? usually people do care about revenue so it should be
SUM(rrprice*quantity)but without quantity - that is useless info.– Alex
Nov 12 '18 at 16:20
1
Note also that as well as having multiple ISBNs, books can be by multiple authors, and dates are generally best stored as dates.
– Strawberry
Nov 12 '18 at 16:37