Get a list of all the tables a specific ID value exists in with SQLite-CodePudding

I am using SQLite v3.31.1 and python 3.7.9.

How do I identify which tables a specific id value exists in? I think something like this exists in MYSQL as "INFORMATION_SCHEMA", but I have not found a SQLite alternative.

I have a table structure that follows what I believe is called "Class Table Inheritance". Basically, I have a product table that has attributes common to all products, plus tables that contain specific attributes to that class of products. The "Products" main table contains an id column that is a primary key, this is used as a foreign key in the child tables. So a specific item exists in both the product table and the child table, but nowhere else (other than some virtual tables for FTS).

My current solution is to get all tables names like

SELECT tbl_name 
FROM sqlite_master 
WHERE type='table'

and then loop over the tables using

SELECT 1
FROM child_table
WHERE id = value

but it seems like there should be a better way.

Thank you.

CodePudding user response：

While the answer I originally posted will work, it is slow. You could add a hash map in python, but it will be slow while the hash map is rebuilt.

Instead, I am using triggers in my SQL setup to create a table with the relevant info. This is a bit more brittle, but much faster for large number of search results as the db is queried just once and the table is loaded when the database is initialized.

My example, -- there is a parent table called Products that has the id primary key.

CREATE TABLE IF NOT EXISTS ThermalFuse (
  id INTEGER NOT NULL, 
  ratedTemperature INTEGER NOT NULL,
  holdingTemperature INTEGER NOT NULL,
  maximumTemperature INTEGER NOT NULL,
  voltage INTEGER NOT NULL,
  current INTEGER NOT NULL,

  FOREIGN KEY (id) REFERENCES Product (id)
);

CREATE TABLE IF NOT EXISTS Table_Hash (
  id INTEGER NOT NULL,
  table_name TEXT NOT NULL,
  FOREIGN KEY (id) REFERENCES Product (id)
);

CREATE TRIGGER ThermalFuse_ai AFTER INSERT ON ThermalFuse
BEGIN
    INSERT INTO Table_Hash (id, table_name)
    VALUES (new.id, 'ThermalFuse');
END;