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]