0
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

user3311313
  • 93
  • 9

1 Answers1

1

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.

Uueerdo
  • 15,723
  • 1
  • 16
  • 21
  • i can not use limit cause i use it for pagination for articles – user3311313 Jul 23 '15 at 17:10
  • The `LIMIT [offset], [# rows]` form of `LIMIT` is often used for that. – Uueerdo Jul 23 '15 at 17:11
  • and i can achieve this with other ways i am just curious why this is not working – user3311313 Jul 23 '15 at 17:24
  • My guess would be that in the first case, the conditions in the WHERE were checked after the results were retrieved, and in the second they were evaluated after the JOIN but before the result fields were calculated. One of the problems many people have with session variables is because the order of evaluation in MySQL can be fluid depending on context. (One way to verify would be to initialize @a to 4 and see if you get no rows in the second query.) – Uueerdo Jul 23 '15 at 17:27