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" <[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]
