[SQL] joining a table whose name is stored in the primary record

[John Gunther]

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 |1|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

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] joining a table whose name is stored in the primary record

[John Gunther]

Andrew Sullivan wrote:

  On Sun, Jun 17, 2007 at 04:39:51AM -0400, John Gunther wrote:
  
  
functions and using subqueries without success. I think I need someone 
to point me in the right conceptual direction.

  
  
Well, the right SQL-esque conceptual direction is not to have
different tables at all.  That's not a very normal-form thing to do,
because the data has been broken into pieces dependent on the data
itself, rather than the kind of data it is.

A
  

Well, Andrew, you're certainly right but I made an exception because of
the data needs. The real application is a sales tax table, where the
tax jurisdiction can depend on any element of the address hierarchy. In
different areas, it could depend on zip/postal code, city,
state/province, nation, or even street address. I originally considered
storing all address elements in one giant table with parent/child
relationships (zip 11208's parent is Brooklyn, Brooklyn's parent is
Kings County, Kings County's parent is NY, etc but brief analysis
showed that address elements frequently have more than one parent. So I
decided to keep the elements in separate tables (nation, city, street,
zip, etc) to allow more complex relationships. That led me to the sales
tax jurisdiction problem. I could just assign a tax jurisdiction to
every street segment but that would create a daunting data maintenance
problem as it requires propagating jurisdiction changes down through
every segment and ensuring every valid street segment has a record.

It's an interesting problem.

Another respondent suggested a programmed function that will do the
job. I was hoping for a pure SQL solution but his approach will
definitely work.

John

Re: [SQL] joining a table whose name is stored in the primary record

[John Gunther]

Andreas Kretschmer wrote:

create or replace function zip_foo(OUT out_id int, OUT out_name text, OUT 
out_name2 text) returns setof record as $$
declare
my_rec  RECORD;
my_name TEXT;
begin
for my_rec in select id, name, parent_tbl, parent_id from zip  LOOP
execute 'select name from ' || my_rec.parent_tbl || ' where id 
= ' || my_rec.parent_id || ';' into my_name;
out_id := my_rec.id;
out_name := my_rec.name;
out_name2 := my_name;
return next;
end loop;
end;
$$ language plpgsql;
  
Thanks, Andrew. I was hoping for a pure SQL solution but your idea will 
certainly work.


John

---(end of broadcast)---
TIP 6: explain analyze is your friend