I am using the following method for doing this, but I am sure it is not the
best one:
(select id, title, author, 'book' as type from books)
union
(select id, title, author, 'cd' as type from cds)
union
(select id, title, author, 'dvd' as type from dvds)
where ...
order by ...
limit ...;
Octavian
----- Original Message -----
From: "Ed Lazor" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Tuesday, June 26, 2007 10:39 PM
Subject: select statement with variable for table_reference?
Is there a way to get something like this to work?
Set @tname="mytable";
Select * from @tname;
Here's what I'm trying to really accomplish in case there is yet another
way
to approach this...
I have to work with product data from multiple databases and multiple
tables. For example, one database has a books table, another database has
a
dvds table and an albums table. One table in my primary database maps
between these other databases and tables. I call this one table
"inventory"
and it has fields for user_id, database_id, table_id, record_id.
select * from inventory where user_id = 'xxx'
I'd like to create one query that selects all of the data for the user's
inventory, independent of which database and table it resides in.
I have some additional tables I can use to help:
inventory_databases with id, name
inventory_tables with id, name
That allows me to get the actual name of the database or table. And that
leads me why I'm trying to find another way to handle the table_reference.
I figure there's a way to specify the value of one table's field as the
name
of the table when doing a join or something.
Any ideas?
Thanks,
Ed
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
