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]
