Why I cannot use this parameter? MYSQL [closed]
0
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
asked Nov 22 '18 at 23:50
arzelcmarzelcm
135
closed as unclear what you're asking by ADyson, Madhur Bhaiya, Sotirios Delimanolis, EJoshuaS, Makyen Nov 23 '18 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 |
0
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
asked Nov 22 '18 at 23:50
arzelcmarzelcm
135
closed as unclear what you're asking by ADyson, Madhur Bhaiya, Sotirios Delimanolis, EJoshuaS, Makyen Nov 23 '18 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'edSELECTs in your query are actually a subquery to the outerSELECTstatement, 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 '18 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 '18 at 0:13
Ah, indeed, but I'm afraid theLIMITclause 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 '18 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 '18 at 0:32
You're welcome. Well, it appears I was wrong regarding the positioning of theLIMITclause. Not really familiar with the MySQL syntax, I apologize.
– Marc.2377
Nov 23 '18 at 1:34
add a comment |
0
0
0
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
asked Nov 22 '18 at 23:50
arzelcmarzelcm
135
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 '18 at 23:50
arzelcmarzelcm
135
asked Nov 22 '18 at 23:50
arzelcmarzelcm
135
asked Nov 22 '18 at 23:50
arzelcmarzelcm
135
asked Nov 22 '18 at 23:50
arzelcmarzelcm
135
asked Nov 22 '18 at 23:50
arzelcmarzelcm
135
135
closed as unclear what you're asking by ADyson, Madhur Bhaiya, Sotirios Delimanolis, EJoshuaS, Makyen Nov 23 '18 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 '18 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'edSELECTs in your query are actually a subquery to the outerSELECTstatement, 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 '18 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 '18 at 0:13
Ah, indeed, but I'm afraid theLIMITclause 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 '18 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 '18 at 0:32
You're welcome. Well, it appears I was wrong regarding the positioning of theLIMITclause. Not really familiar with the MySQL syntax, I apologize.
– Marc.2377
Nov 23 '18 at 1:34
add a comment |
AFAIK the twoUNION'edSELECTs in your query are actually a subquery to the outerSELECTstatement, 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 '18 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 '18 at 0:13
Ah, indeed, but I'm afraid theLIMITclause 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 '18 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 '18 at 0:32
You're welcome. Well, it appears I was wrong regarding the positioning of theLIMITclause. Not really familiar with the MySQL syntax, I apologize.
– Marc.2377
Nov 23 '18 at 1:34
AFAIK the two
UNION'ed SELECTs 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 '18 at 0:05
AFAIK the two
UNION'ed SELECTs 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 at 1:34
add a comment |
1 Answer
1
active
oldest
votes
0
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 '18 at 1:25
Marc.2377Marc.2377
1,83312052
It works perfectly, thanks a lot!
– arzelcm
Nov 23 '18 at 23:26
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
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 '18 at 1:25
Marc.2377Marc.2377
1,83312052
It works perfectly, thanks a lot!
– arzelcm
Nov 23 '18 at 23:26
add a comment |
0
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 '18 at 1:25
Marc.2377Marc.2377
1,83312052
It works perfectly, thanks a lot!
– arzelcm
Nov 23 '18 at 23:26
add a comment |
0
0
0
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 '18 at 1:25
Marc.2377Marc.2377
1,83312052
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 '18 at 1:25
Marc.2377Marc.2377
1,83312052
answered Nov 23 '18 at 1:25
Marc.2377Marc.2377
1,83312052
answered Nov 23 '18 at 1:25
Marc.2377Marc.2377
1,83312052
answered Nov 23 '18 at 1:25
Marc.2377Marc.2377
1,83312052
1,83312052
It works perfectly, thanks a lot!
– arzelcm
Nov 23 '18 at 23:26
add a comment |
It works perfectly, thanks a lot!
– arzelcm
Nov 23 '18 at 23:26
It works perfectly, thanks a lot!
– arzelcm
Nov 23 '18 at 23:26
It works perfectly, thanks a lot!
– arzelcm
Nov 23 '18 at 23:26
add a comment |
AFAIK the two
UNION'edSELECTs in your query are actually a subquery to the outerSELECTstatement, 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 '18 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 '18 at 0:13
Ah, indeed, but I'm afraid the
LIMITclause 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 '18 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 '18 at 0:32
You're welcome. Well, it appears I was wrong regarding the positioning of the
LIMITclause. Not really familiar with the MySQL syntax, I apologize.– Marc.2377
Nov 23 '18 at 1:34