RE: select statement with variable for table_reference?
| 2 | Learning
XML |
| 1 | test | CDs| 1 | Pink Floyd
Meddle |
| 1 | test | DVDs | 2 | Pink Floyd
Live at Pompei |
+-+---+++---
+
4 rows in set (0.31 sec)
Query OK, 0 rows affected, 1 warning (0.33 sec)
mysql CALL test_looping(2);
+-+---+++---
--+
| user_id | database_name | table_name | record_id_in_table | item_name
|
+-+---+++---
--+
| 2 | test | Books | 3 | XML IE5
|
| 2 | test | Books | 4 |
Programming Visual Basis 2005 |
| 2 | test | CDs| 4 | Frank
Marino Mahogany Rush IV |
| 2 | test | DVDs | 3 | Braveheart
|
+-+---+++---
--+
4 rows in set (0.27 sec)
Query OK, 0 rows affected (0.28 sec)
Hope this helps.
Randall Price
Secure Enterprise Technology Initiatives
Microsoft Implementation Group
Virginia Tech Information Technology
1700 Pratt Drive
Blacksburg, VA 24060
Email: [EMAIL PROTECTED]
Phone: (540) 231-4396
-Original Message-
From: Octavian Rasnita [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 27, 2007 2:53 PM
To: Ed Lazor; Price, Randall
Cc: mysql@lists.mysql.com
Subject: Re: select statement with variable for table_reference?
Yes it is a good idea to store in a table information about which other
tables should be searched.
I don't know how these queries can be made using only SQL. I think that
it
could be made in the programming language you use.
For example, first get the list of tables that should be searched (from
that index table), then create that SQL query that uses union using only
those tables.
It shouldn't be too hard to do.
For example, after searching the index table, it could return that you
need
to search in the books and CDS tables.
Then you could create that sql query like:
$sql = ;
for(books, cds) {
$sql .= join union , (select id, title, from $_);
}
$sql .= where ... order by ... limit ...;
So the sql query will search only in the needed tables.
Octavian
- Original Message -
From: Ed Lazor [EMAIL PROTECTED]
To: 'Octavian Rasnita' [EMAIL PROTECTED]; [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Wednesday, June 27, 2007 8:02 PM
Subject: RE: select statement with variable for table_reference?
Hi Octavian,
First, thanks for helping out. I really appreciate it. Thanks to you
also
Randall.
I am not sure I understand what you want.
If you want to search for all cds, and books, and dvds based on a
certain
criteria, you can use that method I've told you about.
Randall said it best. I have one table that has information about
what
other tables to search in.
One table serves as an index of what's in a user's inventory while the
actual product information resides in other tables.
The user inventory table has fields for user_id, database_id,
table_id,
and
record_id.
There are also two other helper tables. One table
(inventory_databases)
contains a list of databases with their id and name. Another table
(inventory_tables) contains a list of tables with their id and name.
Both
of these tables help map from the user's inventory to where product
information resides.
An example record from the inventory table would have data like this:
user_id 33
database_id 1
table_id1
record_id 234234
I can look up the name of the database using database_id in the
inventory_databases table. I can look up the name of the table using
table_id in the inventory_tables table.
All of the other product tables have an id field that corresponds to
the
record_id.
Back to the example above, database_id 1 is the products1 database and
table_id 1 is the books table. That means user id 33 has the book id
234234
in products1.books.
Ok, that describes what I'm working with. As for what I'm trying to
accomplish, I'm trying to reduce the number of queries required for
pulling
together basic information about the user's inventory. Why is that?
Well...
Right now I run one query the inventory table for all information
specific
to the user. Next, I use this information and run additional queries
to
get
the actual product information. If the user has 1000 items in their
inventory, I end up having to run 1 + 1000 queries. This can't be
helped
in
situations where I refer to fields that are unique to each type of
item
(aka
table). It seems like I should be able to avoid this though when
dealing
with a common field like title. I'm just not sure how to go about it.
Using UNIONS is the only single query
Re: select statement with variable for table_reference?
I am not sure I understand what you want. If you want to search for all cds, and books, and dvds based on a certain criteria, you can use that method I've told you about. May you want sometimes to search only in fewer tables that you know before making the query? If yes, then you can create more separate queries that search only in those tables. If you want to search only in the tables that have data about you want to search, you can't do it, because you don't know if those tables contain what you want before searching in them. But if those tables have well defined indexes on the keys you are searching for, that search will be very fast, especially if some of the tables don't contain records that match your criteria. I hope I understood correctly what you want. If you just want to specify a search criteria for each table separately, you can do it for each table in the (select ... where ...) and if you want to specify a search criteria for all the records of those unions, you can do it in a final where ... that's outside of those params. Octavian - Original Message - From: Ed Lazor [EMAIL PROTECTED] To: 'Octavian Rasnita' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, June 26, 2007 11:37 PM Subject: RE: select statement with variable for table_reference? Ok, I used your approach like this: -- select i.scanned_barcode, v.title from inventory as i left join version as v on i.record_id = v.id where i.database_id = '1' AND i.table_id = '1' AND i.user_id = '33' and category_id = '766') UNION (select i.scanned_barcode, v.title from inventory as i left join amg.dvd as v on i.record_id = v.id where i.database_id = '2' AND i.table_id = '3' AND i.user_id = '33' and category_id = '766') order by title DESC -- It works like you're suggesting. I have to add a union for every one of the tables data is being stored in. That means I end up selecting something from every product table, regardless of whether the user actually has something in there or not. Improving on this idea would be finding a way to just query the relevant tables... some sort of conditional union. Any ideas? -Ed -Original Message- From: Octavian Rasnita [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 1:02 PM To: Ed Lazor; mysql@lists.mysql.com Subject: Re: select statement with variable for table_reference? 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select statement with variable for table_reference?
If I understand your problem correctly (and correct me if I am wrong), you have one table that has information about what other tables to search in?? If that is the case, then you can still use the same technique of PREPARED STATEMENTS: Search the first table and retrieve the name of the other table(s) to search, something like this: SET @strOtherTable = (SELECT other_table_name FROM first_table WHERE ...); SET @strSQL = CONCAT(SELECT ... FROM , @strOtherTable, WHERE...); ... ... PREPARE Statement FROM @strSQL; EXECUTE Statement; DEALLOCATE PREPARE Statement; I guess what I am saying here is that the PREPARED STATEMENTS allow you to basically construct any SQL statement you want and works when you want to use a variable in places where they are usually not allowed (i.e., like table names, passing in a variable number of fields for the SELECT statement, the value for the LIMIT clause, etc.) If this is not what you are looking for try to provide a more clear example and I will see what I can do to help out. Thanks, Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -Original Message- From: Ed Lazor [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 6:02 PM To: Price, Randall; mysql@lists.mysql.com Subject: RE: select statement with variable for table_reference? I'm honestly not sure. How would that work when the first table tells you what other tables to pull additional information from? -Original Message- What about using PREPARED STATEMENTS in a stored procedure? Something like: CREATE PROCEDURE `GetInventory`( IN strTableName VARCHAR(50), ...) BEGIN SET @strSQL = CONCAT(SELECT * FROM , strTableName); ... ... PREPARE Statement FROM @strSQL; EXECUTE Statement; DEALLOCATE PREPARE Statement; END Of course, you can build any type of statement to execute using this technique. One thing to know is that the prepared statements don't get put into the query cache. Hope this helps... Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -Original Message- From: Ed Lazor [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 4:37 PM To: 'Octavian Rasnita'; mysql@lists.mysql.com Subject: RE: select statement with variable for table_reference? Ok, I used your approach like this: -- select i.scanned_barcode, v.title from inventory as i left join version as v on i.record_id = v.id where i.database_id = '1' AND i.table_id = '1' AND i.user_id = '33' and category_id = '766') UNION (select i.scanned_barcode, v.title from inventory as i left join amg.dvd as v on i.record_id = v.id where i.database_id = '2' AND i.table_id = '3' AND i.user_id = '33' and category_id = '766') order by title DESC -- 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]
RE: select statement with variable for table_reference?
Hi Octavian, First, thanks for helping out. I really appreciate it. Thanks to you also Randall. I am not sure I understand what you want. If you want to search for all cds, and books, and dvds based on a certain criteria, you can use that method I've told you about. Randall said it best. I have one table that has information about what other tables to search in. One table serves as an index of what's in a user's inventory while the actual product information resides in other tables. The user inventory table has fields for user_id, database_id, table_id, and record_id. There are also two other helper tables. One table (inventory_databases) contains a list of databases with their id and name. Another table (inventory_tables) contains a list of tables with their id and name. Both of these tables help map from the user's inventory to where product information resides. An example record from the inventory table would have data like this: user_id 33 database_id 1 table_id1 record_id 234234 I can look up the name of the database using database_id in the inventory_databases table. I can look up the name of the table using table_id in the inventory_tables table. All of the other product tables have an id field that corresponds to the record_id. Back to the example above, database_id 1 is the products1 database and table_id 1 is the books table. That means user id 33 has the book id 234234 in products1.books. Ok, that describes what I'm working with. As for what I'm trying to accomplish, I'm trying to reduce the number of queries required for pulling together basic information about the user's inventory. Why is that? Well... Right now I run one query the inventory table for all information specific to the user. Next, I use this information and run additional queries to get the actual product information. If the user has 1000 items in their inventory, I end up having to run 1 + 1000 queries. This can't be helped in situations where I refer to fields that are unique to each type of item (aka table). It seems like I should be able to avoid this though when dealing with a common field like title. I'm just not sure how to go about it. Using UNIONS is the only single query to work so far. Like I mentioned though, this requires a UNION for every table that product information is being stored in. If the first table can tell us where the data resides, it seems like we can use it to reduce the number of UNIONS required. Randall, your use of prepared statements and stored procedures seems like a good approach. It might provide a way to dynamically generate the product query. When I look at this: SET @strOtherTable = (SELECT other_table_name FROM first_table WHERE ...); SET @strSQL = CONCAT(SELECT ... FROM , @strOtherTable, WHERE...); The first thing I automatically think of is that the first select will very likely have more than one record in the result set. Is there a way to loop through the results to concatenate everything into the set of UNIONS? Ed May you want sometimes to search only in fewer tables that you know before making the query? If yes, then you can create more separate queries that search only in those tables. If you want to search only in the tables that have data about you want to search, you can't do it, because you don't know if those tables contain what you want before searching in them. But if those tables have well defined indexes on the keys you are searching for, that search will be very fast, especially if some of the tables don't contain records that match your criteria. I hope I understood correctly what you want. If you just want to specify a search criteria for each table separately, you can do it for each table in the (select ... where ...) and if you want to specify a search criteria for all the records of those unions, you can do it in a final where ... that's outside of those params. Octavian - Original Message - From: Ed Lazor [EMAIL PROTECTED] To: 'Octavian Rasnita' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, June 26, 2007 11:37 PM Subject: RE: select statement with variable for table_reference? Ok, I used your approach like this: -- select i.scanned_barcode, v.title from inventory as i left join version as v on i.record_id = v.id where i.database_id = '1' AND i.table_id = '1' AND i.user_id = '33' and category_id = '766') UNION (select i.scanned_barcode, v.title from inventory as i left join amg.dvd as v on i.record_id = v.id where i.database_id = '2' AND i.table_id = '3' AND i.user_id = '33' and category_id = '766') order by title DESC -- It works like you're suggesting. I have to add a union for every one of the tables data is being stored in. That means I end up selecting something
Re: select statement with variable for table_reference?
Yes it is a good idea to store in a table information about which other
tables should be searched.
I don't know how these queries can be made using only SQL. I think that it
could be made in the programming language you use.
For example, first get the list of tables that should be searched (from
that index table), then create that SQL query that uses union using only
those tables.
It shouldn't be too hard to do.
For example, after searching the index table, it could return that you need
to search in the books and CDS tables.
Then you could create that sql query like:
$sql = ;
for(books, cds) {
$sql .= join union , (select id, title, from $_);
}
$sql .= where ... order by ... limit ...;
So the sql query will search only in the needed tables.
Octavian
- Original Message -
From: Ed Lazor [EMAIL PROTECTED]
To: 'Octavian Rasnita' [EMAIL PROTECTED]; [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Wednesday, June 27, 2007 8:02 PM
Subject: RE: select statement with variable for table_reference?
Hi Octavian,
First, thanks for helping out. I really appreciate it. Thanks to you
also
Randall.
I am not sure I understand what you want.
If you want to search for all cds, and books, and dvds based on a certain
criteria, you can use that method I've told you about.
Randall said it best. I have one table that has information about what
other tables to search in.
One table serves as an index of what's in a user's inventory while the
actual product information resides in other tables.
The user inventory table has fields for user_id, database_id, table_id,
and
record_id.
There are also two other helper tables. One table (inventory_databases)
contains a list of databases with their id and name. Another table
(inventory_tables) contains a list of tables with their id and name. Both
of these tables help map from the user's inventory to where product
information resides.
An example record from the inventory table would have data like this:
user_id 33
database_id 1
table_id1
record_id 234234
I can look up the name of the database using database_id in the
inventory_databases table. I can look up the name of the table using
table_id in the inventory_tables table.
All of the other product tables have an id field that corresponds to the
record_id.
Back to the example above, database_id 1 is the products1 database and
table_id 1 is the books table. That means user id 33 has the book id
234234
in products1.books.
Ok, that describes what I'm working with. As for what I'm trying to
accomplish, I'm trying to reduce the number of queries required for
pulling
together basic information about the user's inventory. Why is that?
Well...
Right now I run one query the inventory table for all information specific
to the user. Next, I use this information and run additional queries to
get
the actual product information. If the user has 1000 items in their
inventory, I end up having to run 1 + 1000 queries. This can't be helped
in
situations where I refer to fields that are unique to each type of item
(aka
table). It seems like I should be able to avoid this though when dealing
with a common field like title. I'm just not sure how to go about it.
Using UNIONS is the only single query to work so far. Like I mentioned
though, this requires a UNION for every table that product information is
being stored in. If the first table can tell us where the data resides,
it
seems like we can use it to reduce the number of UNIONS required.
Randall, your use of prepared statements and stored procedures seems like
a
good approach. It might provide a way to dynamically generate the product
query. When I look at this:
SET @strOtherTable = (SELECT other_table_name FROM first_table WHERE
...);
SET @strSQL = CONCAT(SELECT ... FROM , @strOtherTable, WHERE...);
The first thing I automatically think of is that the first select will
very
likely have more than one record in the result set. Is there a way to
loop
through the results to concatenate everything into the set of UNIONS?
Ed
May you want sometimes to search only in fewer tables that you know
before
making the query? If yes, then you can create more separate queries that
search only in those tables.
If you want to search only in the tables that have data about you want to
search, you can't do it, because you don't know if those tables contain
what
you want before searching in them.
But if those tables have well defined indexes on the keys you are
searching
for, that search will be very fast, especially if some of the tables
don't
contain records that match your criteria.
I hope I understood correctly what you want.
If you just want to specify a search criteria for each table separately,
you
can do it for each table in the (select ... where ...) and if you want to
specify a search criteria for all the records of those unions, you can do
it
in a final where ... that's outside
Re: select statement with variable for table_reference?
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]
RE: select statement with variable for table_reference?
Ok, I used your approach like this: -- select i.scanned_barcode, v.title from inventory as i left join version as v on i.record_id = v.id where i.database_id = '1' AND i.table_id = '1' AND i.user_id = '33' and category_id = '766') UNION (select i.scanned_barcode, v.title from inventory as i left join amg.dvd as v on i.record_id = v.id where i.database_id = '2' AND i.table_id = '3' AND i.user_id = '33' and category_id = '766') order by title DESC -- It works like you're suggesting. I have to add a union for every one of the tables data is being stored in. That means I end up selecting something from every product table, regardless of whether the user actually has something in there or not. Improving on this idea would be finding a way to just query the relevant tables... some sort of conditional union. Any ideas? -Ed -Original Message- From: Octavian Rasnita [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 1:02 PM To: Ed Lazor; mysql@lists.mysql.com Subject: Re: select statement with variable for table_reference? 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select statement with variable for table_reference?
Getting back to your original question, I don't know of any way you can use a variable as a table name directly. You can, however, pull off something like this: mysql set @table = stage; Query OK, 0 rows affected (0.06 sec) mysql set @stmt = CONCAT(SELECT * FROM , @table); /* Arbitrarily complex stuff here */ Query OK, 0 rows affected (0.05 sec) mysql prepare foo from @stmt; Query OK, 0 rows affected (0.03 sec) Statement prepared mysql execute foo; +--++ | stage_id | stage_name | +--++ |1 | Imminent | |2 | Incoming | |3 | Follow Up | |4 | Eventual | |5 | Interested | |6 | Ongoing| |7 | Accts | |8 | Dump | |9 | Purchased | +--++ 9 rows in set (0.05 sec) Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Ed Lazor [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 4:37 PM To: 'Octavian Rasnita'; mysql@lists.mysql.com Subject: RE: select statement with variable for table_reference? Ok, I used your approach like this: -- select i.scanned_barcode, v.title from inventory as i left join version as v on i.record_id = v.id where i.database_id = '1' AND i.table_id = '1' AND i.user_id = '33' and category_id = '766') UNION (select i.scanned_barcode, v.title from inventory as i left join amg.dvd as v on i.record_id = v.id where i.database_id = '2' AND i.table_id = '3' AND i.user_id = '33' and category_id = '766') order by title DESC -- It works like you're suggesting. I have to add a union for every one of the tables data is being stored in. That means I end up selecting something from every product table, regardless of whether the user actually has something in there or not. Improving on this idea would be finding a way to just query the relevant tables... some sort of conditional union. Any ideas? -Ed -Original Message- From: Octavian Rasnita [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 1:02 PM To: Ed Lazor; mysql@lists.mysql.com Subject: Re: select statement with variable for table_reference? 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 -- 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]
RE: select statement with variable for table_reference?
What about using PREPARED STATEMENTS in a stored procedure? Something like: CREATE PROCEDURE `GetInventory`( IN strTableName VARCHAR(50), ...) BEGIN SET @strSQL = CONCAT(SELECT * FROM , strTableName); ... ... PREPARE Statement FROM @strSQL; EXECUTE Statement; DEALLOCATE PREPARE Statement; END Of course, you can build any type of statement to execute using this technique. One thing to know is that the prepared statements don't get put into the query cache. Hope this helps... Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -Original Message- From: Ed Lazor [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 4:37 PM To: 'Octavian Rasnita'; mysql@lists.mysql.com Subject: RE: select statement with variable for table_reference? Ok, I used your approach like this: -- select i.scanned_barcode, v.title from inventory as i left join version as v on i.record_id = v.id where i.database_id = '1' AND i.table_id = '1' AND i.user_id = '33' and category_id = '766') UNION (select i.scanned_barcode, v.title from inventory as i left join amg.dvd as v on i.record_id = v.id where i.database_id = '2' AND i.table_id = '3' AND i.user_id = '33' and category_id = '766') order by title DESC -- It works like you're suggesting. I have to add a union for every one of the tables data is being stored in. That means I end up selecting something from every product table, regardless of whether the user actually has something in there or not. Improving on this idea would be finding a way to just query the relevant tables... some sort of conditional union. Any ideas? -Ed -Original Message- From: Octavian Rasnita [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 1:02 PM To: Ed Lazor; mysql@lists.mysql.com Subject: Re: select statement with variable for table_reference? 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 -- 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]
RE: select statement with variable for table_reference?
Thanks for the info Jerry. =) -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 1:59 PM To: 'Ed Lazor'; 'Octavian Rasnita'; mysql@lists.mysql.com Subject: RE: select statement with variable for table_reference? Getting back to your original question, I don't know of any way you can use a variable as a table name directly. You can, however, pull off something like this: mysql set @table = stage; Query OK, 0 rows affected (0.06 sec) mysql set @stmt = CONCAT(SELECT * FROM , @table); /* Arbitrarily complex stuff here */ Query OK, 0 rows affected (0.05 sec) mysql prepare foo from @stmt; Query OK, 0 rows affected (0.03 sec) Statement prepared mysql execute foo; +--++ | stage_id | stage_name | +--++ |1 | Imminent | |2 | Incoming | |3 | Follow Up | |4 | Eventual | |5 | Interested | |6 | Ongoing| |7 | Accts | |8 | Dump | |9 | Purchased | +--++ 9 rows in set (0.05 sec) Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select statement with variable for table_reference?
I'm honestly not sure. How would that work when the first table tells you what other tables to pull additional information from? -Original Message- What about using PREPARED STATEMENTS in a stored procedure? Something like: CREATE PROCEDURE `GetInventory`( IN strTableName VARCHAR(50), ...) BEGIN SET @strSQL = CONCAT(SELECT * FROM , strTableName); ... ... PREPARE Statement FROM @strSQL; EXECUTE Statement; DEALLOCATE PREPARE Statement; END Of course, you can build any type of statement to execute using this technique. One thing to know is that the prepared statements don't get put into the query cache. Hope this helps... Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -Original Message- From: Ed Lazor [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 4:37 PM To: 'Octavian Rasnita'; mysql@lists.mysql.com Subject: RE: select statement with variable for table_reference? Ok, I used your approach like this: -- select i.scanned_barcode, v.title from inventory as i left join version as v on i.record_id = v.id where i.database_id = '1' AND i.table_id = '1' AND i.user_id = '33' and category_id = '766') UNION (select i.scanned_barcode, v.title from inventory as i left join amg.dvd as v on i.record_id = v.id where i.database_id = '2' AND i.table_id = '3' AND i.user_id = '33' and category_id = '766') order by title DESC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
