MySQL - SELECT AS in WHERE
up vote
14
down vote
favorite
5
For some reason, this doesn't work:
select substring(rating, instr(rating,',') +1, +2) as val
from users where val = '15';
It gives this error:
ERROR 1054 (42S22): Unknown column 'val' in 'where clause'
How do I do it then?
mysql sql select
edited Nov 21 at 0:30
Pang
6,8291563101
asked Jan 19 '13 at 11:30
Martin
98128
add a comment |
up vote
14
down vote
favorite
5
For some reason, this doesn't work:
select substring(rating, instr(rating,',') +1, +2) as val
from users where val = '15';
It gives this error:
ERROR 1054 (42S22): Unknown column 'val' in 'where clause'
How do I do it then?
mysql sql select
edited Nov 21 at 0:30
Pang
6,8291563101
asked Jan 19 '13 at 11:30
Martin
98128
2
If you ever find yourself having to process parts of a column, your schema is wrong.
– paxdiablo
Jan 19 '13 at 11:42
add a comment |
up vote
14
down vote
favorite
5
up vote
14
down vote
favorite
5
5
For some reason, this doesn't work:
select substring(rating, instr(rating,',') +1, +2) as val
from users where val = '15';
It gives this error:
ERROR 1054 (42S22): Unknown column 'val' in 'where clause'
How do I do it then?
mysql sql select
edited Nov 21 at 0:30
Pang
6,8291563101
asked Jan 19 '13 at 11:30
Martin
98128
For some reason, this doesn't work:
select substring(rating, instr(rating,',') +1, +2) as val
from users where val = '15';
It gives this error:
ERROR 1054 (42S22): Unknown column 'val' in 'where clause'
How do I do it then?
mysql sql select
mysql sql select
edited Nov 21 at 0:30
Pang
6,8291563101
asked Jan 19 '13 at 11:30
Martin
98128
edited Nov 21 at 0:30
Pang
6,8291563101
asked Jan 19 '13 at 11:30
Martin
98128
edited Nov 21 at 0:30
Pang
6,8291563101
edited Nov 21 at 0:30
Pang
6,8291563101
edited Nov 21 at 0:30
Pang
6,8291563101
6,8291563101
asked Jan 19 '13 at 11:30
Martin
98128
asked Jan 19 '13 at 11:30
Martin
98128
asked Jan 19 '13 at 11:30
Martin
98128
98128
2
If you ever find yourself having to process parts of a column, your schema is wrong.
– paxdiablo
Jan 19 '13 at 11:42
add a comment |
2
If you ever find yourself having to process parts of a column, your schema is wrong.
– paxdiablo
Jan 19 '13 at 11:42
2
2
If you ever find yourself having to process parts of a column, your schema is wrong.
– paxdiablo
Jan 19 '13 at 11:42
If you ever find yourself having to process parts of a column, your schema is wrong.
– paxdiablo
Jan 19 '13 at 11:42
add a comment |
3 Answers
3
active
oldest
votes
up vote
24
down vote
accepted
First, you cannot use ALIAS on the WHERE clause. You shoul be using the column,
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val
FROM users
WHERE SUBSTRING(rating, INSTR(rating,',') +1, +2) = '15'
The reason is as follows: the order of operation is SQL,
- FROM clause
- WHERE clause
- GROUP BY clause
- HAVING clause
- SELECT clause
- ORDER BY clause
the ALIAS takes place on the SELECT clause which is before the WHERE clause.
if you really want to use the alias, wrap it in a subquery,
SELECT *
FROM
(
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val
FROM users
) s
WHERE val = '15'
answered Jan 19 '13 at 11:31
John Woo
204k46386419
Thanks! The idea of the alias was to minimize the select line; a bit strange you cannot do it; But thanks - the above works great.
– Martin
Jan 19 '13 at 11:33
7
The explanation doesn't completely make sense, given that one can use column aliases inGROUP BY,HAVINGandORDER BYclauses.
– eggyal
Jun 10 '13 at 20:29
add a comment |
up vote
3
down vote
val is not defined, it's just an alias. Do it like this:
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val
FROM users
WHERE SUBSTRING(rating, INSTR(rating,',') +1, +2) = 15
edited Nov 21 at 0:29
Pang
6,8291563101
answered Jan 19 '13 at 11:38
echo_Me
32.5k54170
add a comment |
up vote
0
down vote
You can use this query
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2)
FROM users
WHERE SUBSTRING(rating, INSTR(rating,',') +1, +2) = '15'
edited Nov 20 at 10:05
Zain Farooq
1,6552926
answered Nov 20 at 9:49
刘娓娓
1
1
This doesn't seem any better than the existing answers.
– Pang
Nov 20 at 10:02
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',
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
});
}
});
draft saved
draft discarded
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%2f14413867%2fmysql-select-as-in-where%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
up vote
24
down vote
accepted
First, you cannot use ALIAS on the WHERE clause. You shoul be using the column,
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val
FROM users
WHERE SUBSTRING(rating, INSTR(rating,',') +1, +2) = '15'
The reason is as follows: the order of operation is SQL,
- FROM clause
- WHERE clause
- GROUP BY clause
- HAVING clause
- SELECT clause
- ORDER BY clause
the ALIAS takes place on the SELECT clause which is before the WHERE clause.
if you really want to use the alias, wrap it in a subquery,
SELECT *
FROM
(
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val
FROM users
) s
WHERE val = '15'
answered Jan 19 '13 at 11:31
John Woo
204k46386419
Thanks! The idea of the alias was to minimize the select line; a bit strange you cannot do it; But thanks - the above works great.
– Martin
Jan 19 '13 at 11:33
7
The explanation doesn't completely make sense, given that one can use column aliases inGROUP BY,HAVINGandORDER BYclauses.
– eggyal
Jun 10 '13 at 20:29
add a comment |
up vote
24
down vote
accepted
First, you cannot use ALIAS on the WHERE clause. You shoul be using the column,
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val
FROM users
WHERE SUBSTRING(rating, INSTR(rating,',') +1, +2) = '15'
The reason is as follows: the order of operation is SQL,
- FROM clause
- WHERE clause
- GROUP BY clause
- HAVING clause
- SELECT clause
- ORDER BY clause
the ALIAS takes place on the SELECT clause which is before the WHERE clause.
if you really want to use the alias, wrap it in a subquery,
SELECT *
FROM
(
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val
FROM users
) s
WHERE val = '15'
answered Jan 19 '13 at 11:31
John Woo
204k46386419
Thanks! The idea of the alias was to minimize the select line; a bit strange you cannot do it; But thanks - the above works great.
– Martin
Jan 19 '13 at 11:33
7
The explanation doesn't completely make sense, given that one can use column aliases inGROUP BY,HAVINGandORDER BYclauses.
– eggyal
Jun 10 '13 at 20:29
add a comment |
up vote
24
down vote
accepted
up vote
24
down vote
accepted
First, you cannot use ALIAS on the WHERE clause. You shoul be using the column,
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val
FROM users
WHERE SUBSTRING(rating, INSTR(rating,',') +1, +2) = '15'
The reason is as follows: the order of operation is SQL,
- FROM clause
- WHERE clause
- GROUP BY clause
- HAVING clause
- SELECT clause
- ORDER BY clause
the ALIAS takes place on the SELECT clause which is before the WHERE clause.
if you really want to use the alias, wrap it in a subquery,
SELECT *
FROM
(
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val
FROM users
) s
WHERE val = '15'
answered Jan 19 '13 at 11:31
John Woo
204k46386419
First, you cannot use ALIAS on the WHERE clause. You shoul be using the column,
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val
FROM users
WHERE SUBSTRING(rating, INSTR(rating,',') +1, +2) = '15'
The reason is as follows: the order of operation is SQL,
- FROM clause
- WHERE clause
- GROUP BY clause
- HAVING clause
- SELECT clause
- ORDER BY clause
the ALIAS takes place on the SELECT clause which is before the WHERE clause.
if you really want to use the alias, wrap it in a subquery,
SELECT *
FROM
(
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val
FROM users
) s
WHERE val = '15'
answered Jan 19 '13 at 11:31
John Woo
204k46386419
answered Jan 19 '13 at 11:31
John Woo
204k46386419
answered Jan 19 '13 at 11:31
John Woo
204k46386419
answered Jan 19 '13 at 11:31
John Woo
204k46386419
204k46386419
Thanks! The idea of the alias was to minimize the select line; a bit strange you cannot do it; But thanks - the above works great.
– Martin
Jan 19 '13 at 11:33
7
The explanation doesn't completely make sense, given that one can use column aliases inGROUP BY,HAVINGandORDER BYclauses.
– eggyal
Jun 10 '13 at 20:29
add a comment |
Thanks! The idea of the alias was to minimize the select line; a bit strange you cannot do it; But thanks - the above works great.
– Martin
Jan 19 '13 at 11:33
7
The explanation doesn't completely make sense, given that one can use column aliases inGROUP BY,HAVINGandORDER BYclauses.
– eggyal
Jun 10 '13 at 20:29
Thanks! The idea of the alias was to minimize the select line; a bit strange you cannot do it; But thanks - the above works great.
– Martin
Jan 19 '13 at 11:33
Thanks! The idea of the alias was to minimize the select line; a bit strange you cannot do it; But thanks - the above works great.
– Martin
Jan 19 '13 at 11:33
7
7
The explanation doesn't completely make sense, given that one can use column aliases in
GROUP BY, HAVING and ORDER BY clauses.– eggyal
Jun 10 '13 at 20:29
The explanation doesn't completely make sense, given that one can use column aliases in
GROUP BY, HAVING and ORDER BY clauses.– eggyal
Jun 10 '13 at 20:29
add a comment |
up vote
3
down vote
val is not defined, it's just an alias. Do it like this:
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val
FROM users
WHERE SUBSTRING(rating, INSTR(rating,',') +1, +2) = 15
edited Nov 21 at 0:29
Pang
6,8291563101
answered Jan 19 '13 at 11:38
echo_Me
32.5k54170
add a comment |
up vote
3
down vote
val is not defined, it's just an alias. Do it like this:
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val
FROM users
WHERE SUBSTRING(rating, INSTR(rating,',') +1, +2) = 15
edited Nov 21 at 0:29
Pang
6,8291563101
answered Jan 19 '13 at 11:38
echo_Me
32.5k54170
add a comment |
up vote
3
down vote
up vote
3
down vote
val is not defined, it's just an alias. Do it like this:
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val
FROM users
WHERE SUBSTRING(rating, INSTR(rating,',') +1, +2) = 15
edited Nov 21 at 0:29
Pang
6,8291563101
answered Jan 19 '13 at 11:38
echo_Me
32.5k54170
val is not defined, it's just an alias. Do it like this:
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val
FROM users
WHERE SUBSTRING(rating, INSTR(rating,',') +1, +2) = 15
edited Nov 21 at 0:29
Pang
6,8291563101
answered Jan 19 '13 at 11:38
echo_Me
32.5k54170
edited Nov 21 at 0:29
Pang
6,8291563101
edited Nov 21 at 0:29
Pang
6,8291563101
edited Nov 21 at 0:29
Pang
6,8291563101
6,8291563101
answered Jan 19 '13 at 11:38
echo_Me
32.5k54170
answered Jan 19 '13 at 11:38
echo_Me
32.5k54170
answered Jan 19 '13 at 11:38
echo_Me
32.5k54170
32.5k54170
add a comment |
add a comment |
up vote
0
down vote
You can use this query
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2)
FROM users
WHERE SUBSTRING(rating, INSTR(rating,',') +1, +2) = '15'
edited Nov 20 at 10:05
Zain Farooq
1,6552926
answered Nov 20 at 9:49
刘娓娓
1
1
This doesn't seem any better than the existing answers.
– Pang
Nov 20 at 10:02
add a comment |
up vote
0
down vote
You can use this query
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2)
FROM users
WHERE SUBSTRING(rating, INSTR(rating,',') +1, +2) = '15'
edited Nov 20 at 10:05
Zain Farooq
1,6552926
answered Nov 20 at 9:49
刘娓娓
1
1
This doesn't seem any better than the existing answers.
– Pang
Nov 20 at 10:02
add a comment |
up vote
0
down vote
up vote
0
down vote
You can use this query
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2)
FROM users
WHERE SUBSTRING(rating, INSTR(rating,',') +1, +2) = '15'
edited Nov 20 at 10:05
Zain Farooq
1,6552926
answered Nov 20 at 9:49
刘娓娓
1
You can use this query
SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2)
FROM users
WHERE SUBSTRING(rating, INSTR(rating,',') +1, +2) = '15'
edited Nov 20 at 10:05
Zain Farooq
1,6552926
answered Nov 20 at 9:49
刘娓娓
1
edited Nov 20 at 10:05
Zain Farooq
1,6552926
edited Nov 20 at 10:05
Zain Farooq
1,6552926
edited Nov 20 at 10:05
Zain Farooq
1,6552926
1,6552926
answered Nov 20 at 9:49
刘娓娓
1
answered Nov 20 at 9:49
刘娓娓
1
answered Nov 20 at 9:49
刘娓娓
1
1
1
This doesn't seem any better than the existing answers.
– Pang
Nov 20 at 10:02
add a comment |
1
This doesn't seem any better than the existing answers.
– Pang
Nov 20 at 10:02
1
1
This doesn't seem any better than the existing answers.
– Pang
Nov 20 at 10:02
This doesn't seem any better than the existing answers.
– Pang
Nov 20 at 10:02
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f14413867%2fmysql-select-as-in-where%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
2
If you ever find yourself having to process parts of a column, your schema is wrong.
– paxdiablo
Jan 19 '13 at 11:42