Re: select statement with variable for table_reference?

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.

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" <mysql@internetarchitects.biz> To: "'Octavian Rasnita'" <orasnita@gmail.com>; <mysql@lists.mysql.com> Sent: Tuesday, June 26, 2007 11:37 PM Subject: RE: select statement with variable for table_reference?

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

> 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@gmail.com]
>> 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/mysql?unsub=lists@pantek.com