Joining tables on unknown table name

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Can anyone please shed some light on the following...

I have a framework that uses dynamically created tables, named using an
incremental "attribute set ID", as follows:

etc, etc...

I also have another table that stores data for each object within the
framework, called "object". The fields in this table are fixed so are always
known, one of which is "attrset_id" which relates to one of the
"attrdata_***" tables.

The fields in the "attrdata_***" tables are all unknown except for a fixed
"object_id" field that links objects with an entry in those tables.

The idea is that the "attrdata_***" tables can be used to extend the data
for each object so, for example:

We need to extend Object 100 to store new properties "name", "address" and
"tel", so a new "attrdata_4" table is created containing fields "object_id",
"name", "address" and "tel" and "object_id" is set to "100" (the object's
ID) and the "attrset_id" field in the "object" table is set to "4" (the
newly created attribute set's ID).

Now, when querying the DB I want to link the "object" table with the
relevant "attrdata_***" table to get the complete extended data set, so
ideally this would be....

SELECT O.*, A.* FROM object AS O
LEFT JOIN CONCAT("attrdata_", O.attrset_id) AS A ON

... but that doesn't work. What I need to know is if anything along those
lines exists? I've looked, but not found so I'm now making sure :) Although
it could be done using a couple of statements, I'd prefer, if possible, a
single statement solution.

Thanks in advance!

Re: Joining tables on unknown table name

James wrote:
Quoted text here. Click to load it

In SQL, object names are not strings.  You can't use expressions or
parameters in place of table names or field names to make them dynamic
in the way you're doing.  A join is between one specific table and one
other specific table, on every row of the join.

The only time object names are strings is when you're building up a SQL
statement as a string in your application code.  You can do anything you
want here, concatenating partial strings, or substituting application
variables for table names, etc.  But it has to be done before the SQL is
submitted as a statement.

You really should reconsider your schema design.  Use a schema that can
represent your attributes without the need for creating new tables

For instance, there is a schema model called Entity-Attribute-Value
(EAV).  Some relational purists don't like this model.  Claims have been
made that it blurs the distinction between data and metadata, doesn't
scale well, lacks referential integrity, etc.  Still, it is certain to
be a better solution than to create separate tables and then try to join
them together per row, as you're trying to do now.

See also:

Bill K.

Re: Joining tables on unknown table name

Quoted text here. Click to load it

If you have to play games with table names like that, it would
usually be best to add a column to the table, containing the table
name you would have used (or part of it, like the number portion
of "attrdata_3"), and use one table with a fixed name.

Quoted text here. Click to load it

Although it probably violates some principle of normalization, I
have on occasion set up a keyword=value set approach with a table
containing ID (of a person, usually ), attribute code (e.g. name,
home address, work address, home fax, work fax, date of birth, flag
indicating he's on my Christmas card list, etc.), and value (value
of the attribute indicated by the attribute code.  This field is
unfortunately of rather ambiguous type, as it may contain dates,
strings, numbers, etc.).  A given person might have up to a couple
dozen of these records.  A whole "little black book" can be done in
one table, although I typically have another table with all of the
valid attribute codes, an explanation of what it is, and field
label to display, and what order to display them in.

Quoted text here. Click to load it

I doubt it very much.  Oracle even strongly discourages having a
variable table name supplied as a parameter that remains fixed for
the query, much less having it constructed from the fields in a
record you're joining with.

Quoted text here. Click to load it

                    Gordon L. Burditt

Site Timeline