CODEWITHSUNDEEP
sqlite
Nitin Bansal
Nitin Bansal

Reputation: 3030

Refer to current table using "this"-like reference?

I have the following query:

SELECT * from foo where timestamp = (select max(timestamp) from foo)

Where "foo" ofcourse refers to table name.

My problem is that i dont have access to this table "name", but have an api that takes care of referring to a particular table based on a key that is needed to be passed to it. The ApI then accepts second "where clause" argument, and joins the two parts to form a query.

Hence, as required in above mentioned query, i need to know the table name for the inner select statement, which, obviously acts as the "where" clause, but without having table name known.

Is it possible to use something like this:

SELECT * from foo where timestamp = (select max(timestamp) from "this")

where "this" would refer to current table?

I'am completely new to SQL, hence the question might seem a bit stupid if this is the obvious behaviour in SQLite

Thnx in advance...

Upvotes: 0

Views: 638

Answers (1)

Jan Hudec
Jan Hudec

Reputation: 76346

No, SQL does not have anything like that. After all, which table would it refer to when selecting from multiple tables? It allows giving local name to the table (select * from foo as bar), but that can't be used as table name in nested select.

Your only chance is to somehow get your hands at the mapping from key to table name.

If this is in some library that you don't have access to, but can at least get your hands on the database connection (sqlite3 * in C/C++), you could set up authorizer to see which tables the library is reading to reverse-engineer it. You could possibly abuse it to the point that you'd call one query with a where of 'false', so it returns nothing, but the authorizer gets called with the table name you need and than make the real call with correct where. But that's pretty complicated, so try to get the mapping in easier way first.

I should add that it looks to me like a really bad design. Splitting records of the same type across multiple tables is unnecessary complication (sqlite does not have any limit you would care about) and if the types are different, you need to know what it is anyway, so the API should be explicit about the table or at least the mapping rules defined.

Upvotes: 1

Related Questions