sql - How to add a WHERE statement correctly to a complex MySQL query? -

sql - How to add a WHERE statement correctly to a complex MySQL query? -

i have quite complex query info database, sort them , rank them accordingly.

here sql fiddle it: sql fiddle

now want is, add together where statement query, limited users selected (3 users above , 3 users below, id = 8).

where sort between @userpos - 3 , @userpos + 3

so should this, first example:

sql fiddle

i have tried implement statement query, couldn't figure out should add, i've received error (that column cannot found).

any suggestion , / or solution problem? should rewrite whole query this?

if understand correctly, can subquery:

set @userid = 8 select * (select @pos := @pos + 1 sort, points, r.userid, s.active rank r bring together settings s using (userid) cross bring together (select @pos := 0) p s.active = 1 order points desc ) list userid = @userid;

note eliminates layer of subqueries have. otherwise, quite similar query.

edit:

the above based more on sql fiddle on question. (oops.)

to 3 rows before , after given row possible , little tweak, using trick. trick define variable user pos , utilize variable in outer query:

select * (select @pos := @pos + 1 sort, points, r.userid, s.active, if(userid = @userid, @userpos := @pos, 0) rank r bring together settings s using (userid) cross bring together (select @pos := 0, @userpos := 0) p s.active = 1 order points desc ) list `sort` between @userpos - 3 , @userpos + 3;

note: mysql not guarantee order of evaluation variables in select. next bit safer in terms of order of execution:

select * (select (case when (@pos := @pos + 1) null null when (case when (userid = @userid) @userpos := @pos else 1 end) null null else @pos end) sort, points, r.userid, s.active, if(userid = @userid, @userpos := @pos) rank r bring together settings s using (userid) cross bring together (select @pos := 0, @userpos := 0) p s.active = 1 order points desc ) list `sort` between @userpos - 3 , @userpos + 3;

the weird case statements ensure statement executions. is null ensure when clauses fail, assignments made sequentially.

mysql sql