[ic] Searching across multiple tables

[Jimmy Kaplowitz]

Hello Interchangers. I have two tables, a products table and an authors
table, that are related many-to-many (by a glue table called bookauthors
with a column for the products table and a column for the authors table,
among others), and I want to do an interchange 'or' search on two
fields from the products table and two fields from the authors table. how
do I do this? My current search, which is only in one of the tables,
looks like:

[set gensearch]os=yes/fi=products/sf=title/sf=description[/set]

(which is then specified as mv_profile and a search spec provided in
mv_searchspec.)

I would also be glad to use a SQL query joining the various tables, but
I don't know a way to use SQL directly except by using the [query] tag,
which is for in-page searches and not forms or links, or by using the
sq= one-click parameter, which IIRC uses the SQL::Statement module and
parses the text database all the time, while I would prefer to use the
MySQL SQL engine which I have set up to work with my database.

(Wow. I just looked back at the above paragraph-sentence, and it is so
formidable I don't want to mess with it. May you have better luck.)

All help will be greatly appreciated. Thank you.

- Jimmy Kaplowitz
jimmy@kaplowitz.org