Menu
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 ?
439
A subquery-also referred to as an inner query or inner select-is a SELECT statement embedded within a data manipulation language (DML) statement or nested within another subquery. You can use subqueries in SELECT, INSERT, UPDATE, and DELETE statements wherever expressions are allowed.
The result of a SQL SELECT statement is a table.
You should use the WHERE clause to filter the records and fetching only the necessary records. The WHERE clause is not only used in the SELECT statement, but it is also used in the UPDATE, DELETE statement, etc., which we would examine in the subsequent chapters.
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. The original idea was to create the table in beginning of the query, so the (SELECT * FROM #TBL) could be used on the query itself, instead of defining the names on each GROUP BY.
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.
111
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
