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