joining a table whose name is stored in the primary record

Поиск
Список
Период
Сортировка
От John Gunther
Тема joining a table whose name is stored in the primary record
Дата
Msg-id 4674F357.5040409@bucksvsbytes.com
обсуждение исходный текст
Ответы Re: joining a table whose name is stored in the primary record  (Andreas Kretschmer <akretschmer@spamfence.net>)
Re: joining a table whose name is stored in the primary record  (Andrew Sullivan <ajs@crankycanuck.ca>)
Список pgsql-sql
I've tried everything I can think of here to join records when the join 
table varies and is named in the primary record, but to no avail. Here's 
an example with all non-essentials stripped out.

I have 3 tables:

create table zip (id serial primary key,name text,parent_tbl  text,parent_id int
);

create table city (id serial primary key,name text
);

create table county (id serial primary key,name text
);

The zip table has 2 records as follows:
id|name|parent_tbl|parent_id
-----------------------------
1 |10001|city      |12
2 |19999|county    |99

The possible parent tables can be many more than the two examples, city 
and county.

In a single psql statement, I want to retrieve zip records joined with 
the record of their respective parents. The join id is in zip.parent_id 
but the obvious issue is that the join table varies and is only found in 
zip.parent_tbl. Obviously, I can select from zip, then step through the 
results and select the joined data separately for each zip result. How 
can I get these results in one statement? I've tried writing SQL 
functions and using subqueries without success. I think I need someone 
to point me in the right conceptual direction.

Thanks.

John Gunther
Bucks vs Bytes Inc


В списке pgsql-sql по дате отправления:

Предыдущее
От: Rodrigo De León
Дата:
Сообщение: Re: inner join problem with temporary tables
Следующее
От: Andreas Kretschmer
Дата:
Сообщение: Re: joining a table whose name is stored in the primary record