table name as data

Is it possible to select from a table where the table name is itself data
stored in a column?

I Can't easily think of an example (and the real situation I have is rather
complex) but how about something like:

tableA
id tablename
1 person
2 machine
3 user

tableB
id table
1 2
2 2
3 1
4 2

So table A is a list of tablenames (with IDs) and tableB is a list of
affected table IDs. now if each table (person, user, machine) has a primary
field (for arguments sake lets call this "Name")

I want to do something like:

select id, table, name
from
tableB
join
<the table refered to in tableA>

This is where I get stuck. The tablename to join is not "hard-coded" but is
data, being stored in the "tablename" column of tableA.

Does anyone understand this, or am I talking gibberish?

Cheers

Rob

Do You wish do it in a stored procedure ?
If not, simply compose the right code.

I have the same problem but in stored procedure.


"Rob Kings" <greeneggsandham@greymouse.co.uk> ha scritto nel messaggio
news:3triauFu5ls4U1@individual.net...
> Is it possible to select from a table where the table name is itself data
> stored in a column?
>
> I Can't easily think of an example (and the real situation I have is
> rather complex) but how about something like:
>
> tableA
> id tablename
> 1 person
> 2 machine
> 3 user
>
> tableB
> id table
> 1 2
> 2 2
> 3 1
> 4 2
>
> So table A is a list of tablenames (with IDs) and tableB is a list of
> affected table IDs. now if each table (person, user, machine) has a
> primary field (for arguments sake lets call this "Name")
>
> I want to do something like:
>
> select id, table, name
> from
> tableB
> join
> <the table refered to in tableA>
>
> This is where I get stuck. The tablename to join is not "hard-coded" but
> is data, being stored in the "tablename" column of tableA.
>
> Does anyone understand this, or am I talking gibberish?
>
> Cheers
>
> Rob
>
>

Tony

Is that a real answer or are you yanking my chain?

Rob
"Tony" <arcucci@gmail.com> wrote in message
news:6Q1ef.60611$Pe2.1126993@twister2.libero.it...
> Do You wish do it in a stored procedure ?
> If not, simply compose the right code.
>
> I have the same problem but in stored procedure.
>
>
> "Rob Kings" <greeneggsandham@greymouse.co.uk> ha scritto nel messaggio
> news:3triauFu5ls4U1@individual.net...
>> Is it possible to select from a table where the table name is itself data
>> stored in a column?
>>
>> I Can't easily think of an example (and the real situation I have is
>> rather complex) but how about something like:
>>
>> tableA
>> id tablename
>> 1 person
>> 2 machine
>> 3 user
>>
>> tableB
>> id table
>> 1 2
>> 2 2
>> 3 1
>> 4 2
>>
>> So table A is a list of tablenames (with IDs) and tableB is a list of
>> affected table IDs. now if each table (person, user, machine) has a
>> primary field (for arguments sake lets call this "Name")
>>
>> I want to do something like:
>>
>> select id, table, name
>> from
>> tableB
>> join
>> <the table refered to in tableA>
>>
>> This is where I get stuck. The tablename to join is not "hard-coded" but
>> is data, being stored in the "tablename" column of tableA.
>>
>> Does anyone understand this, or am I talking gibberish?
>>
>> Cheers
>>
>> Rob
>>
>>
>
>

>Is it possible to select from a table where the table name is itself data
>stored in a column?

In general, I find it works much better NOT to use a table name or
parts of it as a variable. Add a column and put what you were using
as a table name or the variable part of it into that column. Now
merge all the tables together (this assumes that they have somewhat
the same structure, otherwise queries like you want to do and my
approach will both break). In this way you can just do a join.
You might want to change all your indexes to include the "table
name column".

About the only exception I'd make to this approach is if the tables
are divided for reasons of disk space. You don't necessarily want
40 years of financial records on line all the time in the same table
as the records for the current month, which are quite active.

Gordon L. Burditt

Gordon

Thanks for the suggestions. I don't really want to mess with the table
structure. My example was not the real scenario, I (tried to) simplified
things for the example. What I'm looking at is a logging type application,
where I have a table containing the names of tables that have been altered.
When I report this data I need to dereference from the stored table name (as
text) to the actual table.

I looked further into this using a combination of SET, PREPARE STMT and
EXECUTE STMT. I got pretty close. The problem is that the MySQL Query
Browser doesn't seem to support this syntax, and what works at the
commandline doesn't work in Query Browser, so its anybodies guess as to
whether it will work via ODBC (which is eventually how my code is getting
executed)

Cheers

Rob

"Gordon Burditt" <gordon@hammy.burditt.org> wrote in message
news:11nhgrudo2d2ue4@corp.supernews.com...
> >Is it possible to select from a table where the table name is itself data
>>stored in a column?
>
> In general, I find it works much better NOT to use a table name or
> parts of it as a variable. Add a column and put what you were using
> as a table name or the variable part of it into that column. Now
> merge all the tables together (this assumes that they have somewhat
> the same structure, otherwise queries like you want to do and my
> approach will both break). In this way you can just do a join.
> You might want to change all your indexes to include the "table
> name column".
>
> About the only exception I'd make to this approach is if the tables
> are divided for reasons of disk space. You don't necessarily want
> 40 years of financial records on line all the time in the same table
> as the records for the current month, which are quite active.
>
> Gordon L. Burditt

Rob Kings wrote:
> Is it possible to select from a table where the table name is itself data
> stored in a column?

Most SQL interfaces permit "parameters" so you can prepare a SQL
statement with placeholders (usually denoted with a ? symbol). Then
when you execute the prepared query, give values to substitute for the
parameters. But parameters can be used only in place of a constant
expression. For example:

Legal: SELECT * FROM MyTable WHERE MyField = ?;

Not legal: SELECT * FROM ? WHERE MyField = 123;

Of course, in the application code, a SQL statement is just a string.
You can build up a string however you want, including based on results
from a previous SQL query, and then execute that string.

(pseudocode)
execute "SELECT tablename FROM tableIndex WHERE category = 'A'"
$tablename = result of above
$sql = "SELECT * FROM $tablename WHERE MyField = 123;"
execute $sql

But that requires a multi-step process, executing one query to get the
name of the table, and then executing a second query that you construct
from the results of the first.

Also, if you need to join to a different table per each row of tableB,
forget it. Both sides of any join must remain the same for all rows in
the join.

What you're doing causes RDBMS purists' skin to crawl. They whine that
this is "mixing data and metadata!" Gordon is correct that you need to
design the tables differently to avoid storing similar data in multiple
tables as you're doing.

But since you said you don't want to restructure your database at this
point, and I assume you aren't seeking approval from database academics
;-) so I will just tell you that you can't do what you're trying to do
in one SQL statement; you must build the second statement as a string,
from the results of a previous query that gets the table name.

Regards,
Bill K.

Bill

Thank you. A most concise and precise answer.

Rob
"Bill Karwin" <bill@karwin.com> wrote in message
news:dlbjpo01cer@enews3.newsguy.com...
> Rob Kings wrote:
>> Is it possible to select from a table where the table name is itself data
>> stored in a column?
>
> Most SQL interfaces permit "parameters" so you can prepare a SQL statement
> with placeholders (usually denoted with a ? symbol). Then when you
> execute the prepared query, give values to substitute for the parameters.
> But parameters can be used only in place of a constant expression. For
> example:
>
> Legal: SELECT * FROM MyTable WHERE MyField = ?;
>
> Not legal: SELECT * FROM ? WHERE MyField = 123;
>
> Of course, in the application code, a SQL statement is just a string. You
> can build up a string however you want, including based on results from a
> previous SQL query, and then execute that string.
>
> (pseudocode)
> execute "SELECT tablename FROM tableIndex WHERE category = 'A'"
> $tablename = result of above
> $sql = "SELECT * FROM $tablename WHERE MyField = 123;"
> execute $sql
>
> But that requires a multi-step process, executing one query to get the
> name of the table, and then executing a second query that you construct
> from the results of the first.
>
> Also, if you need to join to a different table per each row of tableB,
> forget it. Both sides of any join must remain the same for all rows in
> the join.
>
> What you're doing causes RDBMS purists' skin to crawl. They whine that
> this is "mixing data and metadata!" Gordon is correct that you need to
> design the tables differently to avoid storing similar data in multiple
> tables as you're doing.
>
> But since you said you don't want to restructure your database at this
> point, and I assume you aren't seeking approval from database academics
> ;-) so I will just tell you that you can't do what you're trying to do in
> one SQL statement; you must build the second statement as a string, from
> the results of a previous query that gets the table name.
>
> Regards,
> Bill K.