How to Use a Returned Column Value as a Table Name in an SQLite Query

Is it possible to use a returned column value as a table name in an SQLite query?

SQLite is designed as an embedded database, i.e., to be used together with a 'real' programming language.
To be able to use such dynamic constructs, you must go outside of SQLite itself:

cursor.execute("SELECT name FROM sqlite_master")
rows = cursor.fetchall()
for row in rows:
    sql = "SELECT ... FROM {} WHERE ...".format(row[0])
    cursor.execute(sql)

SQLite column in table has same name as table

This is not about the table/column name: having a table column with the same name as the table it belongs to is unambiguous in SQL, since the syntax is clear enough about where a column name or a table name is expected.

Table country is not part of the from clause of your query, hence it is not available in the select clause.

You need to join that table, presumably with the incountry table:

inner join country on country.cid = incountry.cid

Then you can add country.country to the select clause.

Query specific column on entire tables in a database

You can use SQLite code to get the sql SELECT statement as a string and execute it to get all the values of the column attachment in all tables of the database:

SELECT GROUP_CONCAT('SELECT ' || pti.name || ' FROM ' || sm.name, ' UNION ALL ') sql
FROM sqlite_master sm CROSS JOIN pragma_table_info(sm.name) pti
WHERE sm.type = 'table' AND pti.name = 'attachment'; 

The above query returns a resultset with only 1 row and 1 column aliased as sql with a value of a string like this:

SELECT attachment FROM table1 
UNION ALL 
SELECT attachment FROM table2 
UNION ALL 
SELECT attachment FROM table4

You can change UNION ALL to UNION inside the function GROUP_CONCAT(), depending on your requirement.

See a simplified demo.

Retrieving SQLite data of one table from multiple values in a specific column of another table

To get matching data for two table use join:

SELECT * FROM IMG_META 
JOIN IMG_REGION ON IMG_META.ImageName=IMG_REGION.ImageName
WHERE IMG_REGION.Country = 'denmark' or IMG_REGION.Country = 'Germany'

---

Related Topics