Mixing
SQL query not showing desired result with multiple AND and OR clauses
My following query is not showing desired result. I don't understand where I am using the brackets wrong
Query:
SELECT `orders`.*, `users`.`fullname`, `users`.`phone`
FROM `orders`
JOIN `users` ON `orders`.`userId` = `users`.`id`
WHERE `orders`.`payment_verified_status` = '1'
AND (`orders`.`restId` = 132 OR `orders`.`restId` = 133 OR `orders`.`restId` = 134)
AND (`rest_brId` != 156 OR `rest_brId` != 188 OR `rest_brId` != 157 OR `rest_brId` != 691 OR `rest_brId` != 158)
The result is coming:
But these rows shouldn't come because I have mentioned in the query that rest_brId != 156
Please help. Thanks!
mysql sql
asked Nov 20 '18 at 11:40
hyd00hyd00
62221029
add a comment |
My following query is not showing desired result. I don't understand where I am using the brackets wrong
Query:
SELECT `orders`.*, `users`.`fullname`, `users`.`phone`
FROM `orders`
JOIN `users` ON `orders`.`userId` = `users`.`id`
WHERE `orders`.`payment_verified_status` = '1'
AND (`orders`.`restId` = 132 OR `orders`.`restId` = 133 OR `orders`.`restId` = 134)
AND (`rest_brId` != 156 OR `rest_brId` != 188 OR `rest_brId` != 157 OR `rest_brId` != 691 OR `rest_brId` != 158)
The result is coming:
But these rows shouldn't come because I have mentioned in the query that rest_brId != 156
Please help. Thanks!
mysql sql
asked Nov 20 '18 at 11:40
hyd00hyd00
62221029
3
Imagine that you were google searching for your exact problem. Would you google "SQL query not showing correct result"? Try rephrase your question so that it is specific
– Zach Smith
Nov 20 '18 at 11:43
2
you did, but you also said " OR rest_brId != 188", and that's true in that row, as are all the others, so overall the AND clause is true, so it returns the row. NOT IN is your friend here.
– ADyson
Nov 20 '18 at 11:43
3
A != 1 or A != 2will be true forA = 1because the expressionA != 2is true
– Cid
Nov 20 '18 at 11:47
add a comment |
My following query is not showing desired result. I don't understand where I am using the brackets wrong
Query:
SELECT `orders`.*, `users`.`fullname`, `users`.`phone`
FROM `orders`
JOIN `users` ON `orders`.`userId` = `users`.`id`
WHERE `orders`.`payment_verified_status` = '1'
AND (`orders`.`restId` = 132 OR `orders`.`restId` = 133 OR `orders`.`restId` = 134)
AND (`rest_brId` != 156 OR `rest_brId` != 188 OR `rest_brId` != 157 OR `rest_brId` != 691 OR `rest_brId` != 158)
The result is coming:
But these rows shouldn't come because I have mentioned in the query that rest_brId != 156
Please help. Thanks!
mysql sql
asked Nov 20 '18 at 11:40
hyd00hyd00
62221029
My following query is not showing desired result. I don't understand where I am using the brackets wrong
Query:
SELECT `orders`.*, `users`.`fullname`, `users`.`phone`
FROM `orders`
JOIN `users` ON `orders`.`userId` = `users`.`id`
WHERE `orders`.`payment_verified_status` = '1'
AND (`orders`.`restId` = 132 OR `orders`.`restId` = 133 OR `orders`.`restId` = 134)
AND (`rest_brId` != 156 OR `rest_brId` != 188 OR `rest_brId` != 157 OR `rest_brId` != 691 OR `rest_brId` != 158)
The result is coming:
But these rows shouldn't come because I have mentioned in the query that rest_brId != 156
Please help. Thanks!
mysql sql
mysql sql
asked Nov 20 '18 at 11:40
hyd00hyd00
62221029
asked Nov 20 '18 at 11:40
hyd00hyd00
62221029
edited Nov 20 '18 at 11:54
hyd00
asked Nov 20 '18 at 11:40
hyd00hyd00
62221029
asked Nov 20 '18 at 11:40
hyd00hyd00
62221029
asked Nov 20 '18 at 11:40
hyd00hyd00
62221029
62221029
3
Imagine that you were google searching for your exact problem. Would you google "SQL query not showing correct result"? Try rephrase your question so that it is specific
– Zach Smith
Nov 20 '18 at 11:43
2
you did, but you also said " OR rest_brId != 188", and that's true in that row, as are all the others, so overall the AND clause is true, so it returns the row. NOT IN is your friend here.
– ADyson
Nov 20 '18 at 11:43
3
A != 1 or A != 2will be true forA = 1because the expressionA != 2is true
– Cid
Nov 20 '18 at 11:47
add a comment |
3
Imagine that you were google searching for your exact problem. Would you google "SQL query not showing correct result"? Try rephrase your question so that it is specific
– Zach Smith
Nov 20 '18 at 11:43
2
you did, but you also said " OR rest_brId != 188", and that's true in that row, as are all the others, so overall the AND clause is true, so it returns the row. NOT IN is your friend here.
– ADyson
Nov 20 '18 at 11:43
3
A != 1 or A != 2will be true forA = 1because the expressionA != 2is true
– Cid
Nov 20 '18 at 11:47
3
3
Imagine that you were google searching for your exact problem. Would you google "SQL query not showing correct result"? Try rephrase your question so that it is specific
– Zach Smith
Nov 20 '18 at 11:43
Imagine that you were google searching for your exact problem. Would you google "SQL query not showing correct result"? Try rephrase your question so that it is specific
– Zach Smith
Nov 20 '18 at 11:43
2
2
you did, but you also said " OR rest_brId != 188", and that's true in that row, as are all the others, so overall the AND clause is true, so it returns the row. NOT IN is your friend here.
– ADyson
Nov 20 '18 at 11:43
you did, but you also said " OR rest_brId != 188", and that's true in that row, as are all the others, so overall the AND clause is true, so it returns the row. NOT IN is your friend here.
– ADyson
Nov 20 '18 at 11:43
3
3
A != 1 or A != 2 will be true for A = 1 because the expression A != 2 is true– Cid
Nov 20 '18 at 11:47
A != 1 or A != 2 will be true for A = 1 because the expression A != 2 is true– Cid
Nov 20 '18 at 11:47
add a comment |
2 Answers
2
active
oldest
votes
Use in:
SELECT o.*, u.`fullname`, u.`phone`
FROM `orders` o JOIN
`users` u
ON o.`userId` = u.`id`
WHERE o.`payment_verified_status` = 1 AND
o.`restId` IN (132, 133, 134) AND
?.`rest_brId` NOT IN (156, 188, 157, 691, 158)
Note the ?. This is for the talbe alias for rest_brID.
The problem with your logic is the OR for the not-equals, not the parentheses. That part of the logic always evaluates to true, because you actually want AND instead of OR for that portion of the logic.
answered Nov 20 '18 at 11:42
Gordon LinoffGordon Linoff
766k35297401
add a comment |
This part of your WHERE clause is incorrect:
(`rest_brId` != 156 OR `rest_brId` != 188 OR `rest_brId` != 157 OR `rest_brId` != 691 OR `rest_brId` != 158)
If rest_brId = 156, although the first part of the clause is not true, the second through fifth parts (e.g. rest_brId != 188) are true, so the row gets into your output. You need to change that line to:
(`rest_brId` != 156 AND `rest_brId` != 188 AND `rest_brId` != 157 AND `rest_brId` != 691 AND `rest_brId` != 158)
or use a NOT IN clause as @GordonLinoff suggests.
answered Nov 20 '18 at 11:45
NickNick
26.6k111839
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%2f53392245%2fsql-query-not-showing-desired-result-with-multiple-and-and-or-clauses%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Use in:
SELECT o.*, u.`fullname`, u.`phone`
FROM `orders` o JOIN
`users` u
ON o.`userId` = u.`id`
WHERE o.`payment_verified_status` = 1 AND
o.`restId` IN (132, 133, 134) AND
?.`rest_brId` NOT IN (156, 188, 157, 691, 158)
Note the ?. This is for the talbe alias for rest_brID.
The problem with your logic is the OR for the not-equals, not the parentheses. That part of the logic always evaluates to true, because you actually want AND instead of OR for that portion of the logic.
answered Nov 20 '18 at 11:42
Gordon LinoffGordon Linoff
766k35297401
add a comment |
Use in:
SELECT o.*, u.`fullname`, u.`phone`
FROM `orders` o JOIN
`users` u
ON o.`userId` = u.`id`
WHERE o.`payment_verified_status` = 1 AND
o.`restId` IN (132, 133, 134) AND
?.`rest_brId` NOT IN (156, 188, 157, 691, 158)
Note the ?. This is for the talbe alias for rest_brID.
The problem with your logic is the OR for the not-equals, not the parentheses. That part of the logic always evaluates to true, because you actually want AND instead of OR for that portion of the logic.
answered Nov 20 '18 at 11:42
Gordon LinoffGordon Linoff
766k35297401
add a comment |
Use in:
SELECT o.*, u.`fullname`, u.`phone`
FROM `orders` o JOIN
`users` u
ON o.`userId` = u.`id`
WHERE o.`payment_verified_status` = 1 AND
o.`restId` IN (132, 133, 134) AND
?.`rest_brId` NOT IN (156, 188, 157, 691, 158)
Note the ?. This is for the talbe alias for rest_brID.
The problem with your logic is the OR for the not-equals, not the parentheses. That part of the logic always evaluates to true, because you actually want AND instead of OR for that portion of the logic.
answered Nov 20 '18 at 11:42
Gordon LinoffGordon Linoff
766k35297401
Use in:
SELECT o.*, u.`fullname`, u.`phone`
FROM `orders` o JOIN
`users` u
ON o.`userId` = u.`id`
WHERE o.`payment_verified_status` = 1 AND
o.`restId` IN (132, 133, 134) AND
?.`rest_brId` NOT IN (156, 188, 157, 691, 158)
Note the ?. This is for the talbe alias for rest_brID.
The problem with your logic is the OR for the not-equals, not the parentheses. That part of the logic always evaluates to true, because you actually want AND instead of OR for that portion of the logic.
answered Nov 20 '18 at 11:42
Gordon LinoffGordon Linoff
766k35297401
answered Nov 20 '18 at 11:42
Gordon LinoffGordon Linoff
766k35297401
answered Nov 20 '18 at 11:42
Gordon LinoffGordon Linoff
766k35297401
answered Nov 20 '18 at 11:42
Gordon LinoffGordon Linoff
766k35297401
766k35297401
add a comment |
add a comment |
This part of your WHERE clause is incorrect:
(`rest_brId` != 156 OR `rest_brId` != 188 OR `rest_brId` != 157 OR `rest_brId` != 691 OR `rest_brId` != 158)
If rest_brId = 156, although the first part of the clause is not true, the second through fifth parts (e.g. rest_brId != 188) are true, so the row gets into your output. You need to change that line to:
(`rest_brId` != 156 AND `rest_brId` != 188 AND `rest_brId` != 157 AND `rest_brId` != 691 AND `rest_brId` != 158)
or use a NOT IN clause as @GordonLinoff suggests.
answered Nov 20 '18 at 11:45
NickNick
26.6k111839
add a comment |
This part of your WHERE clause is incorrect:
(`rest_brId` != 156 OR `rest_brId` != 188 OR `rest_brId` != 157 OR `rest_brId` != 691 OR `rest_brId` != 158)
If rest_brId = 156, although the first part of the clause is not true, the second through fifth parts (e.g. rest_brId != 188) are true, so the row gets into your output. You need to change that line to:
(`rest_brId` != 156 AND `rest_brId` != 188 AND `rest_brId` != 157 AND `rest_brId` != 691 AND `rest_brId` != 158)
or use a NOT IN clause as @GordonLinoff suggests.
answered Nov 20 '18 at 11:45
NickNick
26.6k111839
add a comment |
This part of your WHERE clause is incorrect:
(`rest_brId` != 156 OR `rest_brId` != 188 OR `rest_brId` != 157 OR `rest_brId` != 691 OR `rest_brId` != 158)
If rest_brId = 156, although the first part of the clause is not true, the second through fifth parts (e.g. rest_brId != 188) are true, so the row gets into your output. You need to change that line to:
(`rest_brId` != 156 AND `rest_brId` != 188 AND `rest_brId` != 157 AND `rest_brId` != 691 AND `rest_brId` != 158)
or use a NOT IN clause as @GordonLinoff suggests.
answered Nov 20 '18 at 11:45
NickNick
26.6k111839
This part of your WHERE clause is incorrect:
(`rest_brId` != 156 OR `rest_brId` != 188 OR `rest_brId` != 157 OR `rest_brId` != 691 OR `rest_brId` != 158)
If rest_brId = 156, although the first part of the clause is not true, the second through fifth parts (e.g. rest_brId != 188) are true, so the row gets into your output. You need to change that line to:
(`rest_brId` != 156 AND `rest_brId` != 188 AND `rest_brId` != 157 AND `rest_brId` != 691 AND `rest_brId` != 158)
or use a NOT IN clause as @GordonLinoff suggests.
answered Nov 20 '18 at 11:45
NickNick
26.6k111839
answered Nov 20 '18 at 11:45
NickNick
26.6k111839
answered Nov 20 '18 at 11:45
NickNick
26.6k111839
answered Nov 20 '18 at 11:45
NickNick
26.6k111839
26.6k111839
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.
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%2f53392245%2fsql-query-not-showing-desired-result-with-multiple-and-and-or-clauses%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
Imagine that you were google searching for your exact problem. Would you google "SQL query not showing correct result"? Try rephrase your question so that it is specific
– Zach Smith
Nov 20 '18 at 11:43
2
you did, but you also said " OR rest_brId != 188", and that's true in that row, as are all the others, so overall the AND clause is true, so it returns the row. NOT IN is your friend here.
– ADyson
Nov 20 '18 at 11:43
3
A != 1 or A != 2will be true forA = 1because the expressionA != 2is true– Cid
Nov 20 '18 at 11:47