Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id B5BEB9FB469
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 19 Jun 2007 15:03:06 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 22892-08 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 19 Jun 2007 15:02:58 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id E2D7B9FBB9D
	for <pgsql-sql@postgresql.org>; Tue, 19 Jun 2007 15:02:57 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3
Received: from elasmtp-curtail.atl.sa.earthlink.net
	(elasmtp-curtail.atl.sa.earthlink.net [209.86.89.64])
	by svr4.postgresql.org (Postfix) with ESMTP id 542FF5AF027
	for <pgsql-sql@postgresql.org>; Tue, 19 Jun 2007 18:02:57 +0000 (GMT)
Received: from [69.86.105.158] (helo=[192.168.0.102])
	by elasmtp-curtail.atl.sa.earthlink.net with asmtp (Exim 4.34)
	id 1I0i2Z-0001IS-Mb
	for pgsql-sql@postgresql.org; Tue, 19 Jun 2007 14:02:55 -0400
Message-ID: <46781A46.9010708@bucksvsbytes.com>
Date: Tue, 19 Jun 2007 14:02:46 -0400
From: John Gunther <postgresql@bucksvsbytes.com>
User-Agent: Thunderbird 1.5.0.12 (Windows/20070509)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: joining a table whose name is stored in the primary record
References: <4674F357.5040409@bucksvsbytes.com>
	<20070617155441.GB13671@phlogiston.dyndns.org>
In-Reply-To: <20070617155441.GB13671@phlogiston.dyndns.org>
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-ELNK-Trace: 
 012f15a0baae39ad94f5150ab1c16ac0c864d17e3813b99a4565f68070fc4d29457a526307911426350badd9bab72f9c350badd9bab72f9c350badd9bab72f9c
X-Originating-IP: 69.86.105.158
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL=-0.001,
	BAYES_50=0.001, HTML_MESSAGE=0.001, MIME_HTML_ONLY=0.001
X-Spam-Level: 
X-Archive-Number: 200706/143
X-Sequence-Number: 28633

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Andrew Sullivan wrote:
<blockquote cite="mid20070617155441.GB13671@phlogiston.dyndns.org"
 type="cite">
  <pre wrap="">On Sun, Jun 17, 2007 at 04:39:51AM -0400, John Gunther wrote:
  </pre>
  <blockquote type="cite">
    <pre wrap="">functions and using subqueries without success. I think I need someone 
to point me in the right conceptual direction.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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
  </pre>
</blockquote>
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.<br>
<br>
It's an interesting problem.<br>
<br>
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.<br>
<br>
John<br>
</body>
</html>