CODEWITHSUNDEEP
mysqlsql
user3311313
user3311313

Reputation: 103

User defined variables does not work on where clause

SET @a:=0

The below query works

SELECT *,@a:=@a+1 FROM article WHERE @a<=3

This query returns 3 rows But when the query contains LEFT JOIN it does not work, returns all rows.

SELECT a.*,@a:=@a+1 FROM article a
LEFT JOIN comments c ON c.aid=a.id
WHERE @a<=3

I don't understand why this query returns all rows

Upvotes: 0

Views: 80

Answers (1)

Uueerdo
Uueerdo

Reputation: 15961

LIMIT 3 would be a much clearer expression of what (it looks like) you are trying to do. That said, I have been known to borderline abuse session variables, and even I tend to balk at seeing them in WHERE clauses; it is generally inadvisable to cross clauses with them.

....though this could work for you:

SELECT a.*,@a:=@a+1 AS theAValue
FROM article a
LEFT JOIN comments c ON c.aid=a.id
HAVING theAValue <=3
;

HAVING is always processed after all the result fields have been calculated.

Upvotes: 1

Related Questions