db: find table name by id

It's possible to find a table name by an id? I have multiple tables that extends another table, when I find the id in the "super" table, I'd like to know in which subtable is that id. There is a way with indexing to do that or another way? Th开发者_运维知识库anks

Your example is really unclear but ...

article(stuff,id) news(id references article(id),morestuff) courses(id references article(id),evenmorestuff)

So you're using the id from article to link to either news or courses and would like to avoid select union from news AND courses ?

1) your current query does not contain this information

2) A few ways to do this:

a) you change your datamodel to something more generic (article,r_article_news,news,r_article_courses,courses) and determine that on this basis

b) you add the information in the article table (add a column named type, where you will enter either news or courses)

c) you make your first query slightly heavier to determine that automatically

SELECT a.*,b.type 
FROM article a 
INNER JOIN 
    (SELECT id,'news' AS type 
    FROM news 
    UNION 
    SELECT id,'courses' AS type 
    FROM courses) b 
ON a.id=b.id;

Tags say mysql and postgresql. Since it is not clear which one you want, here is the solution for mysql.

Obviosly, you can do this only if your tables have a FOREIGN KEYS set. For example, if you have a database dogs_and_peaople with two tables:

dogs
------
id
owner_id -- foreign key on people.id
name

people
------
id
name
surname

If you want to find out, which table and field are reperenced by dogs.owner_id, you can look in information_schema database, like this:

SELECT 
    REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM
    information_schema.KEY_COLUMN_USAGE
WHERE
    CONSTRAINT_SCHEMA = 'dogs_and_peaople' -- database name
    AND TABLE_NAME = 'dogs'
    AND COLUMN_NAME = 'owner_id'