0

Hi everyone

I have my query but it doesn't work and I know why but I'm asking you if you can help me, I will explain. Requests aren't logic, but it's not the question.

SELECT (5+5) AS result FROM house WHERE id = (5+5); [WORK]
SELECT (5+5) AS result FROM house LIMIT (5+5);  [#1064 - You have an error in your SQL syntax]
SELECT (5+5) AS result FROM house WHERE id = result; [#1054 - Unknown column 'result' in 'where clause']

Do you have any idea to fix that? I understand errors, but I'm asking if I can do with other way.

Thank you.

roeygol
  • 4,908
  • 9
  • 51
  • 88
JonasGame
  • 25
  • 8
  • I don't know what your question is. You have one working query, why don't you want to use that? – AdamMc331 May 04 '15 at 00:18
  • possible duplicate of [Can you use an alias in the WHERE clause in mysql?](http://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql) – AdamMc331 May 04 '15 at 00:19
  • McAdam331: I can't use HAVING because I need to compare result with id... – JonasGame May 04 '15 at 00:23
  • I want to improve it with less repeat if it's possible! – JonasGame May 04 '15 at 00:24
  • I don't think there is, see the question I linked. – AdamMc331 May 04 '15 at 00:26
  • I see but I can't use house.id = result with HAVING, so the question and answers didn't help me. – JonasGame May 04 '15 at 00:28
  • The first answer mentions that in the WHERE clause you would have to repeat your expression, just like what you have now. – AdamMc331 May 04 '15 at 00:29
  • Yes but maybe there is a solution because I don't use MySQL Function... I don't know. – JonasGame May 04 '15 at 00:31
  • 1
    The order of execution is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT (although MySQL has been extended to allow a few non-standard things). Your second query violates the MySQL rule that LIMIT can take 1 or 2 integer constants (not a calculation). Your third query won't work because the WHERE has been evaluated long before the SELECT fields are even considered. – Nicholai May 04 '15 at 00:57

1 Answers1

0

http://sqlfiddle.com/#!9/7a2c7/2

SET @result = 5+5;
SELECT id, @result
FROM house 
WHERE id = @result;

http://sqlfiddle.com/#!9/7a2c7/8

SELECT id, @idx:=(5+5) as result
FROM house 
HAVING id  = result;

http://sqlfiddle.com/#!9/7a2c7/10

SET @limit:=3;
SET @s = CONCAT('SELECT id, @idx:=(5+5) as result
FROM house 
LIMIT ',@limit); 
PREPARE stmt1 FROM @s; 
EXECUTE stmt1;
Alex
  • 16,739
  • 1
  • 28
  • 51
  • Hey, thank you for your JS's code. My query is: SELECT id, x, y, z, SQRT( ((100-x) * (100-y)) ) AS distance FROM house WHERE distance < 5. It will works with a variable like your first example? Sorry, I can't try currently. Thank you for your help Alex. – JonasGame May 04 '15 at 21:11