0

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.

cpx
  • 17,009
  • 20
  • 87
  • 142

2 Answers2

2

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

Marius
  • 57,995
  • 32
  • 132
  • 151
  • Adding a LIMIT 1 may be obscuring other faults; perhaps his query is only supposed to ever return a single row. You might want (!) an error if it doesn't. – Joe Feb 13 '10 at 02:05
  • It returns the 'full_name' from the table matching column 4. I guess I would need to use something like a for loop to get each 'full_name'. – cpx Feb 13 '10 at 02:39
1

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).

Fredrik Ullner
  • 2,106
  • 2
  • 22
  • 28