Nested statements in sqlite

Question

I'm using the sqlite3 library in c++ to query the database from *.sqlite file. can you write a query statement in sqlite3 like:

char* sql = "select name from table id = (select full_name from second_table where column = 4);"

The second statement should return an id to complete the query statement with first statement.

Answer 1

Yes you can, just make sure that the nested query doesn't return more than one row. Add a LIMIT 1 to the end of the nested query to fix this. Also make sure that it always returns a row, or else the main query will not work.

If you want to match several rows in the nested query, then you can use either IN, like so:

char* sql = "select name from table WHERE id IN (select full_name from second_table where column = 4);"

or you can use JOIN:

char* sql = "select name from table JOIN second_table ON table.id = second_table.full_name WHERE second_table.column = 4"

Note that the IN method can be very slow, and that JOIN can be very fast, if you index on the right columns

Answer 2

On a sidenote, you can use SQLiteadmin (http://sqliteadmin.orbmu2k.de/) to view the database and make queries directly in it (useful for testing etc).