Why I cannot use this parameter? MYSQL [closed]
I'm trying to use the "id" in both selects on the UNION, is there any way to do it?
SELECT id, name, (
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a WHERE ag.id = id AND agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type != 3
UNION
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a, user_analysis ua WHERE agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type = 3 AND ua.analysis_id = a.id AND a.id = '5bf1d6c5242f1' ORDER BY last_update LIMIT 1
) AS last_update_date
FROM analysis_groups WHERE id != '0' ORDER BY last_update_date DESC LIMIT 20;
mysql mysql-error-1064
closed as unclear what you're asking by ADyson, Madhur Bhaiya, Sotirios Delimanolis, EJoshuaS, Makyen Nov 23 at 23:18
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
I'm trying to use the "id" in both selects on the UNION, is there any way to do it?
SELECT id, name, (
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a WHERE ag.id = id AND agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type != 3
UNION
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a, user_analysis ua WHERE agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type = 3 AND ua.analysis_id = a.id AND a.id = '5bf1d6c5242f1' ORDER BY last_update LIMIT 1
) AS last_update_date
FROM analysis_groups WHERE id != '0' ORDER BY last_update_date DESC LIMIT 20;
mysql mysql-error-1064
closed as unclear what you're asking by ADyson, Madhur Bhaiya, Sotirios Delimanolis, EJoshuaS, Makyen Nov 23 at 23:18
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
AFAIK the twoUNION
'edSELECT
s in your query are actually a subquery to the outerSELECT
statement, and you can't have a subquery return more than one row like that. Are you getting an error message? What does it say? Also, consider simplifying your code by removing non-essential column and table names. See: How to create a Minimal, Complete, and Verifiable example
– Marc.2377
Nov 23 at 0:05
@Marc.2377 Actually is not returning more than one row, there is a LIMIT at the end of the UNION. I'm having this: #1052 - Column 'id' in where clause is ambiguous
– arzelcm
Nov 23 at 0:13
Ah, indeed, but I'm afraid theLIMIT
clause is in fact at the end of one component of the UNION, when it should be at the end of both (or perhaps moving it outside of the ending parenthesis should work; I can't test it right now - and maybe you'll also want to do the same with the ORDER BY clause). Concerning your main error, though, I'll post an answer soon.
– Marc.2377
Nov 23 at 0:18
It makes sense but not working. LIMIT at the end of the UNION is effecting to the absolute UNION query, I mean: It attachs the two SELECTS and from these 5 registers (for ex.) it gets the first one, isn't it?? Thanks for your answering @Marc.2377
– arzelcm
Nov 23 at 0:32
You're welcome. Well, it appears I was wrong regarding the positioning of theLIMIT
clause. Not really familiar with the MySQL syntax, I apologize.
– Marc.2377
Nov 23 at 1:34
add a comment |
I'm trying to use the "id" in both selects on the UNION, is there any way to do it?
SELECT id, name, (
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a WHERE ag.id = id AND agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type != 3
UNION
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a, user_analysis ua WHERE agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type = 3 AND ua.analysis_id = a.id AND a.id = '5bf1d6c5242f1' ORDER BY last_update LIMIT 1
) AS last_update_date
FROM analysis_groups WHERE id != '0' ORDER BY last_update_date DESC LIMIT 20;
mysql mysql-error-1064
I'm trying to use the "id" in both selects on the UNION, is there any way to do it?
SELECT id, name, (
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a WHERE ag.id = id AND agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type != 3
UNION
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a, user_analysis ua WHERE agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type = 3 AND ua.analysis_id = a.id AND a.id = '5bf1d6c5242f1' ORDER BY last_update LIMIT 1
) AS last_update_date
FROM analysis_groups WHERE id != '0' ORDER BY last_update_date DESC LIMIT 20;
mysql mysql-error-1064
mysql mysql-error-1064
asked Nov 22 at 23:50
arzelcm
135
135
closed as unclear what you're asking by ADyson, Madhur Bhaiya, Sotirios Delimanolis, EJoshuaS, Makyen Nov 23 at 23:18
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as unclear what you're asking by ADyson, Madhur Bhaiya, Sotirios Delimanolis, EJoshuaS, Makyen Nov 23 at 23:18
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
AFAIK the twoUNION
'edSELECT
s in your query are actually a subquery to the outerSELECT
statement, and you can't have a subquery return more than one row like that. Are you getting an error message? What does it say? Also, consider simplifying your code by removing non-essential column and table names. See: How to create a Minimal, Complete, and Verifiable example
– Marc.2377
Nov 23 at 0:05
@Marc.2377 Actually is not returning more than one row, there is a LIMIT at the end of the UNION. I'm having this: #1052 - Column 'id' in where clause is ambiguous
– arzelcm
Nov 23 at 0:13
Ah, indeed, but I'm afraid theLIMIT
clause is in fact at the end of one component of the UNION, when it should be at the end of both (or perhaps moving it outside of the ending parenthesis should work; I can't test it right now - and maybe you'll also want to do the same with the ORDER BY clause). Concerning your main error, though, I'll post an answer soon.
– Marc.2377
Nov 23 at 0:18
It makes sense but not working. LIMIT at the end of the UNION is effecting to the absolute UNION query, I mean: It attachs the two SELECTS and from these 5 registers (for ex.) it gets the first one, isn't it?? Thanks for your answering @Marc.2377
– arzelcm
Nov 23 at 0:32
You're welcome. Well, it appears I was wrong regarding the positioning of theLIMIT
clause. Not really familiar with the MySQL syntax, I apologize.
– Marc.2377
Nov 23 at 1:34
add a comment |
AFAIK the twoUNION
'edSELECT
s in your query are actually a subquery to the outerSELECT
statement, and you can't have a subquery return more than one row like that. Are you getting an error message? What does it say? Also, consider simplifying your code by removing non-essential column and table names. See: How to create a Minimal, Complete, and Verifiable example
– Marc.2377
Nov 23 at 0:05
@Marc.2377 Actually is not returning more than one row, there is a LIMIT at the end of the UNION. I'm having this: #1052 - Column 'id' in where clause is ambiguous
– arzelcm
Nov 23 at 0:13
Ah, indeed, but I'm afraid theLIMIT
clause is in fact at the end of one component of the UNION, when it should be at the end of both (or perhaps moving it outside of the ending parenthesis should work; I can't test it right now - and maybe you'll also want to do the same with the ORDER BY clause). Concerning your main error, though, I'll post an answer soon.
– Marc.2377
Nov 23 at 0:18
It makes sense but not working. LIMIT at the end of the UNION is effecting to the absolute UNION query, I mean: It attachs the two SELECTS and from these 5 registers (for ex.) it gets the first one, isn't it?? Thanks for your answering @Marc.2377
– arzelcm
Nov 23 at 0:32
You're welcome. Well, it appears I was wrong regarding the positioning of theLIMIT
clause. Not really familiar with the MySQL syntax, I apologize.
– Marc.2377
Nov 23 at 1:34
AFAIK the two
UNION
'ed SELECT
s in your query are actually a subquery to the outer SELECT
statement, and you can't have a subquery return more than one row like that. Are you getting an error message? What does it say? Also, consider simplifying your code by removing non-essential column and table names. See: How to create a Minimal, Complete, and Verifiable example– Marc.2377
Nov 23 at 0:05
AFAIK the two
UNION
'ed SELECT
s in your query are actually a subquery to the outer SELECT
statement, and you can't have a subquery return more than one row like that. Are you getting an error message? What does it say? Also, consider simplifying your code by removing non-essential column and table names. See: How to create a Minimal, Complete, and Verifiable example– Marc.2377
Nov 23 at 0:05
@Marc.2377 Actually is not returning more than one row, there is a LIMIT at the end of the UNION. I'm having this: #1052 - Column 'id' in where clause is ambiguous
– arzelcm
Nov 23 at 0:13
@Marc.2377 Actually is not returning more than one row, there is a LIMIT at the end of the UNION. I'm having this: #1052 - Column 'id' in where clause is ambiguous
– arzelcm
Nov 23 at 0:13
Ah, indeed, but I'm afraid the
LIMIT
clause is in fact at the end of one component of the UNION, when it should be at the end of both (or perhaps moving it outside of the ending parenthesis should work; I can't test it right now - and maybe you'll also want to do the same with the ORDER BY clause). Concerning your main error, though, I'll post an answer soon.– Marc.2377
Nov 23 at 0:18
Ah, indeed, but I'm afraid the
LIMIT
clause is in fact at the end of one component of the UNION, when it should be at the end of both (or perhaps moving it outside of the ending parenthesis should work; I can't test it right now - and maybe you'll also want to do the same with the ORDER BY clause). Concerning your main error, though, I'll post an answer soon.– Marc.2377
Nov 23 at 0:18
It makes sense but not working. LIMIT at the end of the UNION is effecting to the absolute UNION query, I mean: It attachs the two SELECTS and from these 5 registers (for ex.) it gets the first one, isn't it?? Thanks for your answering @Marc.2377
– arzelcm
Nov 23 at 0:32
It makes sense but not working. LIMIT at the end of the UNION is effecting to the absolute UNION query, I mean: It attachs the two SELECTS and from these 5 registers (for ex.) it gets the first one, isn't it?? Thanks for your answering @Marc.2377
– arzelcm
Nov 23 at 0:32
You're welcome. Well, it appears I was wrong regarding the positioning of the
LIMIT
clause. Not really familiar with the MySQL syntax, I apologize.– Marc.2377
Nov 23 at 1:34
You're welcome. Well, it appears I was wrong regarding the positioning of the
LIMIT
clause. Not really familiar with the MySQL syntax, I apologize.– Marc.2377
Nov 23 at 1:34
add a comment |
1 Answer
1
active
oldest
votes
The short answer would be: Define an alias for analysis_groups
in the main query, and reference it in the WHERE
clause, like this
SELECT id, name, (
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a WHERE ag.id = ag2.id AND agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type != 3
UNION
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a, user_analysis ua WHERE agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type = 3 AND ua.analysis_id = a.id AND a.id = '5bf1d6c5242f1' ORDER BY last_update LIMIT 1
) AS last_update_date
FROM analysis_groups ag2 WHERE id != '0' ORDER BY last_update_date DESC LIMIT 20;
It works perfectly, thanks a lot!
– arzelcm
Nov 23 at 23:26
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The short answer would be: Define an alias for analysis_groups
in the main query, and reference it in the WHERE
clause, like this
SELECT id, name, (
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a WHERE ag.id = ag2.id AND agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type != 3
UNION
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a, user_analysis ua WHERE agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type = 3 AND ua.analysis_id = a.id AND a.id = '5bf1d6c5242f1' ORDER BY last_update LIMIT 1
) AS last_update_date
FROM analysis_groups ag2 WHERE id != '0' ORDER BY last_update_date DESC LIMIT 20;
It works perfectly, thanks a lot!
– arzelcm
Nov 23 at 23:26
add a comment |
The short answer would be: Define an alias for analysis_groups
in the main query, and reference it in the WHERE
clause, like this
SELECT id, name, (
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a WHERE ag.id = ag2.id AND agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type != 3
UNION
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a, user_analysis ua WHERE agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type = 3 AND ua.analysis_id = a.id AND a.id = '5bf1d6c5242f1' ORDER BY last_update LIMIT 1
) AS last_update_date
FROM analysis_groups ag2 WHERE id != '0' ORDER BY last_update_date DESC LIMIT 20;
It works perfectly, thanks a lot!
– arzelcm
Nov 23 at 23:26
add a comment |
The short answer would be: Define an alias for analysis_groups
in the main query, and reference it in the WHERE
clause, like this
SELECT id, name, (
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a WHERE ag.id = ag2.id AND agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type != 3
UNION
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a, user_analysis ua WHERE agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type = 3 AND ua.analysis_id = a.id AND a.id = '5bf1d6c5242f1' ORDER BY last_update LIMIT 1
) AS last_update_date
FROM analysis_groups ag2 WHERE id != '0' ORDER BY last_update_date DESC LIMIT 20;
The short answer would be: Define an alias for analysis_groups
in the main query, and reference it in the WHERE
clause, like this
SELECT id, name, (
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a WHERE ag.id = ag2.id AND agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type != 3
UNION
SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a, user_analysis ua WHERE agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type = 3 AND ua.analysis_id = a.id AND a.id = '5bf1d6c5242f1' ORDER BY last_update LIMIT 1
) AS last_update_date
FROM analysis_groups ag2 WHERE id != '0' ORDER BY last_update_date DESC LIMIT 20;
answered Nov 23 at 1:25
Marc.2377
1,80911650
1,80911650
It works perfectly, thanks a lot!
– arzelcm
Nov 23 at 23:26
add a comment |
It works perfectly, thanks a lot!
– arzelcm
Nov 23 at 23:26
It works perfectly, thanks a lot!
– arzelcm
Nov 23 at 23:26
It works perfectly, thanks a lot!
– arzelcm
Nov 23 at 23:26
add a comment |
AFAIK the two
UNION
'edSELECT
s in your query are actually a subquery to the outerSELECT
statement, and you can't have a subquery return more than one row like that. Are you getting an error message? What does it say? Also, consider simplifying your code by removing non-essential column and table names. See: How to create a Minimal, Complete, and Verifiable example– Marc.2377
Nov 23 at 0:05
@Marc.2377 Actually is not returning more than one row, there is a LIMIT at the end of the UNION. I'm having this: #1052 - Column 'id' in where clause is ambiguous
– arzelcm
Nov 23 at 0:13
Ah, indeed, but I'm afraid the
LIMIT
clause is in fact at the end of one component of the UNION, when it should be at the end of both (or perhaps moving it outside of the ending parenthesis should work; I can't test it right now - and maybe you'll also want to do the same with the ORDER BY clause). Concerning your main error, though, I'll post an answer soon.– Marc.2377
Nov 23 at 0:18
It makes sense but not working. LIMIT at the end of the UNION is effecting to the absolute UNION query, I mean: It attachs the two SELECTS and from these 5 registers (for ex.) it gets the first one, isn't it?? Thanks for your answering @Marc.2377
– arzelcm
Nov 23 at 0:32
You're welcome. Well, it appears I was wrong regarding the positioning of the
LIMIT
clause. Not really familiar with the MySQL syntax, I apologize.– Marc.2377
Nov 23 at 1:34