CODEWITHSUNDEEP
mysqlwhere-clause
Kevin
Kevin

Reputation: 11662

select on results of a select

I have a database table that records movements of users in a game. Each time they move I record their user id, the move_index (increments each row) and the id of the zone they are in. I have a special move_index value of -1 to indicate the last movement of that 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:

I know how to do this with multiple SQL statements & java - but I don't know how to do this in a single SQL statement. Do I need to do a select and then a select on the results of this select ?

Upvotes: 9

Views: 11873

Answers (3)

DoubleZero
DoubleZero

Reputation: 31

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

Upvotes: 0

myAces
myAces

Reputation: 699

here goes your query:

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

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

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

Upvotes: 0

John Parker
John Parker

Reputation: 54415

You can simply carry out a SUBQUERY to achieve this within a "single" query.

e.g.: SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

In essence, you're using the results of the "inner" SELECT as the working data set for the "outer" SELECT.

Upvotes: 10

Related Questions