Professional Web Applications Themes

table name as data - MySQL

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

  1. #1

    Default 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


    Rob Kings Guest

  2. #2

    Default Re: table name as data

    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" <greeneggsandhamgreymouse.co.uk> ha scritto nel messaggio
    news:3triauFu5ls4U1individual.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 Guest

  3. #3

    Default Re: table name as data

    Tony

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

    Rob
    "Tony" <arcucci> wrote in message
    news:6Q1ef.60611$Pe2.1126993twister2.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" <greeneggsandhamgreymouse.co.uk> ha scritto nel messaggio
    > news:3triauFu5ls4U1individual.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
    >>
    >>
    >
    >

    Rob Kings Guest

  4. #4

    Default Re: table name as data

    >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 Burditt Guest

  5. #5

    Default Re: table name as data

    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" <gordonhammy.burditt.org> wrote in message
    news:11nhgrudo2d2ue4corp.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 Guest

  6. #6

    Default Re: table name as data

    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 Karwin Guest

  7. #7

    Default Re: table name as data

    Bill

    Thank you. A most concise and precise answer.

    Rob
    "Bill Karwin" <billkarwin.com> wrote in message
    news:dlbjpo01cerenews3.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.


    Rob Kings Guest

Similar Threads

  1. Replies: 0
    Last Post: June 1st, 03:15 AM
  2. Replies: 4
    Last Post: October 27th, 03:01 PM
  3. Trouble copying data from old table to new table
    By RelentlessMike in forum Coldfusion Database Access
    Replies: 3
    Last Post: May 19th, 05:01 PM
  4. Replies: 2
    Last Post: March 8th, 11:57 AM
  5. Replies: 1
    Last Post: July 9th, 03:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139