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]
