Select by selection

Question

I have a database table that records user movements in the game. Each time they move, I write down my user ID, move_index (increments each line) and the identifier of the zone in which they are located. I have a special value move_index -1 to indicate the last movement of this user.

id user_id move_index zone_id ---------------------------------------------------------------- 0 11 0 0 1 11 1 0 2 11 2 0 3 11 -1 3 4 22 0 0 5 22 1 1 6 22 2 1 7 22 -1 3 

I want to do two things with sql:

• Detection of all users who started and ended in specific zones (for example, started in zone 0 and ended in zone 3)
• Extension of the foregoing, the discovery of all users who started and finished in certain zones and went through a certain zone.

I know how to do this with multiple SQL and java statements, but I don't know how to do this in a single SQL statement. Do I need to make a choice, and then choose according to the results of this choice?

Answer 1

You can just do SUBQUERY to achieve this in a single query.

for example: SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

Essentially, you use the results of an "internal" SELECT as a working dataset for an "external" SELECT.

Answer 2

 select distinct user_id from table where (move_index=0 and zone_id=0) and (move_index=-1 and zone_id=3) 

Answer 3

here is your request:

 SELECT *, (foo = true AND bar = true) as foobar FROM foo WHERE (id = 3) 

You can use the foobar value in the query results to determine the result of the second equation.

I think this should give you a hint on how to do this.