Why an syntax error when I made alias in the nested query?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
SELECT customers.Name
FROM
(SELECT *
FROM customers AS c
INNER JOIN orders AS o
ON c.Id=o.CustomerId) AS co
WHERE customers.Name NOT IN co; #Syntax error: co
19:21:06 Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'co' at line 7 0.000 sec
Thank you!
mysql sql
asked Jan 4 at 0:26
Wing FanWing Fan
32
add a comment |
SELECT customers.Name
FROM
(SELECT *
FROM customers AS c
INNER JOIN orders AS o
ON c.Id=o.CustomerId) AS co
WHERE customers.Name NOT IN co; #Syntax error: co
19:21:06 Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'co' at line 7 0.000 sec
Thank you!
mysql sql
asked Jan 4 at 0:26
Wing FanWing Fan
32
3
Did you check the manual for the right syntax as advised?
– Martin Smith
Jan 4 at 0:33
1
You cannot use an alias as operator toIN. And sincecois also the relation you select from it wouldn't make much sense to exclude all names in there. Please edit your question and elaborate on what you're trying to do.
– sticky bit
Jan 4 at 0:33
cois defined as a table, but you're using it like a column with theNOT INoperator. I would expect something likeWHERE customer.Name NOT IN co.Name.
– Alejandro
Jan 4 at 0:35
That query attempt doesn't make much sense. What are you trying to do? Shiw us some sample table data and the expected result - all as formatted text, not images.
– jarlh
Jan 4 at 7:52
add a comment |
SELECT customers.Name
FROM
(SELECT *
FROM customers AS c
INNER JOIN orders AS o
ON c.Id=o.CustomerId) AS co
WHERE customers.Name NOT IN co; #Syntax error: co
19:21:06 Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'co' at line 7 0.000 sec
Thank you!
mysql sql
asked Jan 4 at 0:26
Wing FanWing Fan
32
SELECT customers.Name
FROM
(SELECT *
FROM customers AS c
INNER JOIN orders AS o
ON c.Id=o.CustomerId) AS co
WHERE customers.Name NOT IN co; #Syntax error: co
19:21:06 Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'co' at line 7 0.000 sec
Thank you!
mysql sql
mysql sql
asked Jan 4 at 0:26
Wing FanWing Fan
32
asked Jan 4 at 0:26
Wing FanWing Fan
32
edited Jan 4 at 0:30
Wing Fan
asked Jan 4 at 0:26
Wing FanWing Fan
32
asked Jan 4 at 0:26
Wing FanWing Fan
32
asked Jan 4 at 0:26
Wing FanWing Fan
32
32
3
Did you check the manual for the right syntax as advised?
– Martin Smith
Jan 4 at 0:33
1
You cannot use an alias as operator toIN. And sincecois also the relation you select from it wouldn't make much sense to exclude all names in there. Please edit your question and elaborate on what you're trying to do.
– sticky bit
Jan 4 at 0:33
cois defined as a table, but you're using it like a column with theNOT INoperator. I would expect something likeWHERE customer.Name NOT IN co.Name.
– Alejandro
Jan 4 at 0:35
That query attempt doesn't make much sense. What are you trying to do? Shiw us some sample table data and the expected result - all as formatted text, not images.
– jarlh
Jan 4 at 7:52
add a comment |
3
Did you check the manual for the right syntax as advised?
– Martin Smith
Jan 4 at 0:33
1
You cannot use an alias as operator toIN. And sincecois also the relation you select from it wouldn't make much sense to exclude all names in there. Please edit your question and elaborate on what you're trying to do.
– sticky bit
Jan 4 at 0:33
cois defined as a table, but you're using it like a column with theNOT INoperator. I would expect something likeWHERE customer.Name NOT IN co.Name.
– Alejandro
Jan 4 at 0:35
That query attempt doesn't make much sense. What are you trying to do? Shiw us some sample table data and the expected result - all as formatted text, not images.
– jarlh
Jan 4 at 7:52
3
3
Did you check the manual for the right syntax as advised?
– Martin Smith
Jan 4 at 0:33
Did you check the manual for the right syntax as advised?
– Martin Smith
Jan 4 at 0:33
1
1
You cannot use an alias as operator to
IN. And since co is also the relation you select from it wouldn't make much sense to exclude all names in there. Please edit your question and elaborate on what you're trying to do.– sticky bit
Jan 4 at 0:33
You cannot use an alias as operator to
IN. And since co is also the relation you select from it wouldn't make much sense to exclude all names in there. Please edit your question and elaborate on what you're trying to do.– sticky bit
Jan 4 at 0:33
co is defined as a table, but you're using it like a column with the NOT IN operator. I would expect something like WHERE customer.Name NOT IN co.Name.– Alejandro
Jan 4 at 0:35
co is defined as a table, but you're using it like a column with the NOT IN operator. I would expect something like WHERE customer.Name NOT IN co.Name.– Alejandro
Jan 4 at 0:35
That query attempt doesn't make much sense. What are you trying to do? Shiw us some sample table data and the expected result - all as formatted text, not images.
– jarlh
Jan 4 at 7:52
That query attempt doesn't make much sense. What are you trying to do? Shiw us some sample table data and the expected result - all as formatted text, not images.
– jarlh
Jan 4 at 7:52
add a comment |
2 Answers
2
active
oldest
votes
IN() or NOT IN() predicates must have a parenthesized list of values or subquery. You don't have any parens in your predicate.
Also even if you had parens, you can't compare a column to an alias like that. In the context of a WHERE clause, a column is a scalar, but an alias is a set of columns.
I guess you're looking for customers who have no orders. Here's two ways of getting that:
SELECT c.Name
FROM customers AS c
WHERE c.Id NOT IN (SELECT CustomerId FROM orders);
Or:
SELECT c.Name
FROM customers AS c
LEFT OUTER JOIN orders AS o ON c.Id=o.CustomerId
WHERE o.CustomerId IS NULL;
answered Jan 4 at 0:41
Bill KarwinBill Karwin
385k64521680
add a comment |
Probably you want to get the names of customers who have not placed orders
but instead of placing the joined query after the NOT IN operator you placed it after FROM.
If this is the case, use this:
SELECT Name
FROM customers
WHERE Name NOT IN (
SELECT c.Name
FROM customers AS c
INNER JOIN orders AS o
ON c.Id=o.CustomerId
)
answered Jan 4 at 0:41
forpasforpas
20.1k4830
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%2f54031654%2fwhy-an-syntax-error-when-i-made-alias-in-the-nested-query%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
IN() or NOT IN() predicates must have a parenthesized list of values or subquery. You don't have any parens in your predicate.
Also even if you had parens, you can't compare a column to an alias like that. In the context of a WHERE clause, a column is a scalar, but an alias is a set of columns.
I guess you're looking for customers who have no orders. Here's two ways of getting that:
SELECT c.Name
FROM customers AS c
WHERE c.Id NOT IN (SELECT CustomerId FROM orders);
Or:
SELECT c.Name
FROM customers AS c
LEFT OUTER JOIN orders AS o ON c.Id=o.CustomerId
WHERE o.CustomerId IS NULL;
answered Jan 4 at 0:41
Bill KarwinBill Karwin
385k64521680
add a comment |
IN() or NOT IN() predicates must have a parenthesized list of values or subquery. You don't have any parens in your predicate.
Also even if you had parens, you can't compare a column to an alias like that. In the context of a WHERE clause, a column is a scalar, but an alias is a set of columns.
I guess you're looking for customers who have no orders. Here's two ways of getting that:
SELECT c.Name
FROM customers AS c
WHERE c.Id NOT IN (SELECT CustomerId FROM orders);
Or:
SELECT c.Name
FROM customers AS c
LEFT OUTER JOIN orders AS o ON c.Id=o.CustomerId
WHERE o.CustomerId IS NULL;
answered Jan 4 at 0:41
Bill KarwinBill Karwin
385k64521680
add a comment |
IN() or NOT IN() predicates must have a parenthesized list of values or subquery. You don't have any parens in your predicate.
Also even if you had parens, you can't compare a column to an alias like that. In the context of a WHERE clause, a column is a scalar, but an alias is a set of columns.
I guess you're looking for customers who have no orders. Here's two ways of getting that:
SELECT c.Name
FROM customers AS c
WHERE c.Id NOT IN (SELECT CustomerId FROM orders);
Or:
SELECT c.Name
FROM customers AS c
LEFT OUTER JOIN orders AS o ON c.Id=o.CustomerId
WHERE o.CustomerId IS NULL;
answered Jan 4 at 0:41
Bill KarwinBill Karwin
385k64521680
IN() or NOT IN() predicates must have a parenthesized list of values or subquery. You don't have any parens in your predicate.
Also even if you had parens, you can't compare a column to an alias like that. In the context of a WHERE clause, a column is a scalar, but an alias is a set of columns.
I guess you're looking for customers who have no orders. Here's two ways of getting that:
SELECT c.Name
FROM customers AS c
WHERE c.Id NOT IN (SELECT CustomerId FROM orders);
Or:
SELECT c.Name
FROM customers AS c
LEFT OUTER JOIN orders AS o ON c.Id=o.CustomerId
WHERE o.CustomerId IS NULL;
answered Jan 4 at 0:41
Bill KarwinBill Karwin
385k64521680
answered Jan 4 at 0:41
Bill KarwinBill Karwin
385k64521680
answered Jan 4 at 0:41
Bill KarwinBill Karwin
385k64521680
answered Jan 4 at 0:41
Bill KarwinBill Karwin
385k64521680
385k64521680
add a comment |
add a comment |
Probably you want to get the names of customers who have not placed orders
but instead of placing the joined query after the NOT IN operator you placed it after FROM.
If this is the case, use this:
SELECT Name
FROM customers
WHERE Name NOT IN (
SELECT c.Name
FROM customers AS c
INNER JOIN orders AS o
ON c.Id=o.CustomerId
)
answered Jan 4 at 0:41
forpasforpas
20.1k4830
add a comment |
Probably you want to get the names of customers who have not placed orders
but instead of placing the joined query after the NOT IN operator you placed it after FROM.
If this is the case, use this:
SELECT Name
FROM customers
WHERE Name NOT IN (
SELECT c.Name
FROM customers AS c
INNER JOIN orders AS o
ON c.Id=o.CustomerId
)
answered Jan 4 at 0:41
forpasforpas
20.1k4830
add a comment |
Probably you want to get the names of customers who have not placed orders
but instead of placing the joined query after the NOT IN operator you placed it after FROM.
If this is the case, use this:
SELECT Name
FROM customers
WHERE Name NOT IN (
SELECT c.Name
FROM customers AS c
INNER JOIN orders AS o
ON c.Id=o.CustomerId
)
answered Jan 4 at 0:41
forpasforpas
20.1k4830
Probably you want to get the names of customers who have not placed orders
but instead of placing the joined query after the NOT IN operator you placed it after FROM.
If this is the case, use this:
SELECT Name
FROM customers
WHERE Name NOT IN (
SELECT c.Name
FROM customers AS c
INNER JOIN orders AS o
ON c.Id=o.CustomerId
)
answered Jan 4 at 0:41
forpasforpas
20.1k4830
answered Jan 4 at 0:41
forpasforpas
20.1k4830
answered Jan 4 at 0:41
forpasforpas
20.1k4830
answered Jan 4 at 0:41
forpasforpas
20.1k4830
20.1k4830
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%2f54031654%2fwhy-an-syntax-error-when-i-made-alias-in-the-nested-query%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
Did you check the manual for the right syntax as advised?
– Martin Smith
Jan 4 at 0:33
1
You cannot use an alias as operator to
IN. And sincecois also the relation you select from it wouldn't make much sense to exclude all names in there. Please edit your question and elaborate on what you're trying to do.– sticky bit
Jan 4 at 0:33
cois defined as a table, but you're using it like a column with theNOT INoperator. I would expect something likeWHERE customer.Name NOT IN co.Name.– Alejandro
Jan 4 at 0:35
That query attempt doesn't make much sense. What are you trying to do? Shiw us some sample table data and the expected result - all as formatted text, not images.
– jarlh
Jan 4 at 7:52