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:mysql@stripped]
> Sent: Tuesday, June 26, 2007 4:37 PM
> To: 'Octavian Rasnita'; mysql@stripped
> 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:orasnita@stripped]
> > Sent: Tuesday, June 26, 2007 1:02 PM
> > To: Ed Lazor; mysql@stripped
> > 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/mysql?unsub=1
>
>
