Thread: Using table name in column for joining

Hello,

I have a column in a table that contains the name of another table,
and the id in that table.

I would like to use this in a join statement. Is that possible?

example:

create table animal (
  id serial,
  table_name varchar(8) default 'monkey' CHECK (table_name IN ('monkey', 'elephant')),
  table_id integer
);

create table monkey (
   id serial,
   name varchar(20)
);

create table elephant (
   id serial,
   name varchar(20)
);

and I want to do:

    select animal.name from animal left join animal.table_name on animaltablename.id = animal.table_id;

or something like that.

I know it's probably considered bad SQL, but what else can I do?

Thanks,
Antonio

On Tue, Jan 5, 2010 at 3:30 PM, Antonio Goméz Soto
<antonio.gomez.soto@gmail.com> wrote:
> Hello,
>
> I have a column in a table that contains the name of another table,
> and the id in that table.
>
> I would like to use this in a join statement. Is that possible?

not possible I'm afraid.

But have a look at concept of inheritance for something that might
suit you. Or just store everything in one table, but add new id to
each row. That is what other half of the world uses.

Also, when writing queries like that, consider using aliases for table
name, for instance:

select a.id from animals a;

makes life easier.

hth

--
GJ

Op 05-01-10 18:00, sabrina miller schreef:
> You can also have inheritance from animal, in such case you can have
> partitioned different animals in different tables with their special
> attributes in it.
>
> then you will have:
>
>
> create table animal(
>    id serial,
>    name varchar(20),
>    age integer
>    );
>
> create table elephant
>          (some_attr_that_only_have_
> elephants varchar(20)
>          )
>          INHERITS (animal);
>

This is PostgreSQL-specific isn't it?
I'm a bit afraid to use brand-specific constructs.

Antonio


> create table monkey
>          (some_attr_that_only_have_monkey varchar(20)
>          )
>          INHERITS (animal);
>
> insert into elephant (name, age, some_attr_that_only_have_elephants)
> values ('sophie',15,'lorem');
>
> insert into monkey (name, age, some_attr_that_only_have_monkey)
> values ('lory',3,'impsu');
>
> You can look for diferents animals like this:
>
> select name <http://animal.name/> from animal;
> select name <http://animal.name/> from elephant;
> select name <http://animal.name/> from monkey;
>
> I hope it helps.
>
>
> 2010/1/5 Grzegorz Jaśkiewicz <gryzman@gmail.com <mailto:gryzman@gmail.com>>
>
>     On Tue, Jan 5, 2010 at 3:30 PM, Antonio Goméz Soto
>     <antonio.gomez.soto@gmail.com <mailto:antonio.gomez.soto@gmail.com>>
>     wrote:
>      > Hello,
>      >
>      > I have a column in a table that contains the name of another table,
>      > and the id in that table.
>      >
>      > I would like to use this in a join statement. Is that possible?
>
>     not possible I'm afraid.
>
>     But have a look at concept of inheritance for something that might
>     suit you. Or just store everything in one table, but add new id to
>     each row. That is what other half of the world uses.
>
>     Also, when writing queries like that, consider using aliases for table
>     name, for instance:
>
>     select a.id <http://a.id> from animals a;
>
>     makes life easier.
>
>     hth
>
>     --
>     GJ
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>
>