From: John Gunther <postgresql@bucksvsbytes.com>
To: pgsql-sql@postgresql.org
Subject: joining a table whose name is stored in the primary record
Date: Sun, 17 Jun 2007 04:39:51 -0400
Message-ID: <4674F357.5040409@bucksvsbytes.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@bucksvsbytes.com
Subject: Re: joining a table whose name is stored in the primary record
In-Reply-To: <4674F357.5040409@bucksvsbytes.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox