(Sorry for such a long post... I thought others may benefit as well as
ED)
Ed,
Here is an example using a CURSOR and a TEMPORARY TABLE to do something
similar to what you want (I think!):
========== CREATE TABLES ==========
mysql> show create table mydatabases;
CREATE TABLE `mydatabases` (
`database_id` int(11) NOT NULL auto_increment,
`database_name` varchar(50) NOT NULL,
PRIMARY KEY (`database_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
mysql> show create table mytables;
CREATE TABLE `mytables` (
`table_id` int(11) NOT NULL auto_increment,
`database_id` int(11) NOT NULL,
`table_name` varchar(50) NOT NULL,
PRIMARY KEY (`table_id`),
KEY `FK_mytables_mydatabases` (`database_id`),
CONSTRAINT `FK_mytables_mydatabases` FOREIGN KEY (`database_id`)
REFERENCES `mydatabases` (`database_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
mysql> show create table myrecords;
CREATE TABLE `myrecords` (
`record_id` int(11) NOT NULL auto_increment,
`table_id` int(11) NOT NULL,
`record_id_in_table` int(11) NOT NULL,
PRIMARY KEY (`record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
========== TEST DATA ==========
mysql> select * from mydatabases;
+-------------+---------------+
| database_id | database_name |
+-------------+---------------+
| 1 | test |
| 2 | test2 |
| 3 | test3 |
+-------------+---------------+
3 rows in set (0.00 sec)
mysql> select * from mytables;
+----------+-------------+------------+
| table_id | database_id | table_name |
+----------+-------------+------------+
| 1 | 1 | Books |
| 2 | 1 | CDs |
| 3 | 1 | DVDs |
| 4 | 1 | Records |
+----------+-------------+------------+
4 rows in set (0.00 sec)
mysql> select * from myrecords;
+-----------+----------+--------------------+
| record_id | table_id | record_id_in_table |
+-----------+----------+--------------------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 1 | 5 |
| 6 | 2 | 1 |
| 7 | 2 | 2 |
| 8 | 2 | 3 |
| 9 | 2 | 4 |
| 10 | 3 | 1 |
| 11 | 3 | 2 |
| 12 | 3 | 3 |
| 13 | 3 | 4 |
| 14 | 3 | 5 |
| 15 | 3 | 6 |
+-----------+----------+--------------------+
15 rows in set (0.00 sec)
mysql> select * from myinventory;
+--------------+---------+-------------+----------+-----------+
| inventory_id | user_id | database_id | table_id | record_id |
+--------------+---------+-------------+----------+-----------+
| 1 | 1 | 1 | 1 | 1 |
| 2 | 1 | 1 | 1 | 2 |
| 3 | 1 | 1 | 2 | 1 |
| 4 | 1 | 1 | 3 | 2 |
| 5 | 2 | 1 | 1 | 3 |
| 6 | 2 | 1 | 1 | 4 |
| 7 | 2 | 1 | 2 | 4 |
| 8 | 2 | 1 | 3 | 3 |
+--------------+---------+-------------+----------+-----------+
8 rows in set (0.02 sec)
mysql> select * from books;
+---------+-------------------------------+
| book_id | book_name |
+---------+-------------------------------+
| 1 | MySQL Tutorial |
| 2 | Learning XML |
| 3 | XML IE5 |
| 4 | Programming Visual Basis 2005 |
| 5 | Learning C# |
+---------+-------------------------------+
5 rows in set (0.00 sec)
mysql> select * from cds;
+-------+---------------------------------+
| cd_id | cd_name |
+-------+---------------------------------+
| 1 | Pink Floyd Meddle |
| 2 | Rush Chronicles |
| 3 | Led Zepplin IV |
| 4 | Frank Marino & Mahogany Rush IV |
+-------+---------------------------------+
4 rows in set (0.00 sec)
mysql> select * from dvds;
+--------+---------------------------+
| dvd_id | dvd_name |
+--------+---------------------------+
| 1 | Dances With Wolves |
| 2 | Pink Floyd Live at Pompei |
| 3 | Braveheart |
| 4 | Saving Private Ryan |
| 5 | Ice Age |
| 6 | Cars |
+--------+---------------------------+
6 rows in set (0.00 sec)
========== STORED PROCEDURE ==========
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`test_looping`$$
CREATE PROCEDURE `test_looping`(IN intUserID int )
BEGIN
/*
-------------------------------------------------------------------
Declare variables to use.
-------------------------------------------------------------------
*/
DECLARE blnNoMoreRows BOOLEAN DEFAULT FALSE;
DECLARE strDatabaseName VARCHAR(50);
DECLARE strTableName VARCHAR(50);
DECLARE intRecordIdInTable INT(11);
DECLARE strFieldID VARCHAR(50);
DECLARE strFieldName VARCHAR(50);
DECLARE strSQL VARCHAR(2000);
/*
-------------------------------------------------------------------
Declare CURSOR and HANDLER.
-------------------------------------------------------------------
*/
DECLARE Cursor1 CURSOR FOR
SELECT
d.database_name,
t.table_name,
r.record_id_in_table
FROM
myInventory i
JOIN myDatabases d ON i.database_id = d.database_id
JOIN myTables t ON i.table_id = t.table_id
JOIN myRecords r ON i.record_id = r.record_id
WHERE
i.user_id = intUserID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET blnNoMoreRows = TRUE;
/*
-------------------------------------------------------------------
Create a temporary table to hold the results.
-------------------------------------------------------------------
*/
DROP TEMPORARY TABLE IF EXISTS TempResults;
CREATE TEMPORARY TABLE TempResults (
user_id INT(11) NOT NULL,
database_name VARCHAR(50) NOT NULL,
table_name VARCHAR(50) NOT NULL,
record_id_in_table INT(11) NOT NULL,
item_name VARCHAR(50) NOT NULL);
/*
-------------------------------------------------------------------
Loop through cursor.
-------------------------------------------------------------------
*/
OPEN Cursor1;
REPEAT
FETCH Cursor1 INTO
strDatabaseName,
strTableName,
intRecordIdInTable;
IF NOT blnNoMoreRows THEN
CASE LOWER(strTableName)
WHEN "books" THEN
SET strFieldID = "book_id";
SET strFieldName = "book_name";
WHEN "cds" THEN
SET strFieldID = "cd_id";
SET strFieldName = "cd_name";
WHEN "dvds" THEN
SET strFieldID = "dvd_id";
SET strFieldName = "dvd_name";
END CASE;
-- Here is where you build the dynamic SQL
SET @strSQL = CONCAT(
"SET @strItemName = ",
"(",
"SELECT ", strFieldName,
" FROM `",
strDatabaseName,"`.`",strTableName,"`",
" WHERE ", strFieldID, " = ",
intRecordIdInTable,
")");
PREPARE stmt1 FROM @strSQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
-- Save these results into the temporary table
INSERT INTO
TempResults
SELECT
intUserID,
strDatabaseName,
strTableName,
intRecordIdInTable,
@strItemName;
END IF;
UNTIL blnNoMoreRows END REPEAT;
CLOSE Cursor1;
/*
-------------------------------------------------------------------
Return the results from the temporary table.
-------------------------------------------------------------------
*/
SELECT * FROM TempResults;
END$$
DELIMITER ;
========== TEST CALLS ==========
mysql> CALL test_looping(1);
+---------+---------------+------------+--------------------+-----------
----------------+
| user_id | database_name | table_name | record_id_in_table | item_name
|
+---------+---------------+------------+--------------------+-----------
----------------+
| 1 | test | Books | 1 | MySQL
Tutorial |
| 1 | test | Books | 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_id 1
> 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
>> > 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]
