[ic] Cascaded query against multiple tables
Kevin Walsh
interchange-users@icdevgroup.org
Wed Jul 2 23:54:00 EDT 2003
>
> I need to run a query against table1. Based on what I find there, I
> need to take the results, and search with them against table2 and
> table3.
>
> Table1 has a list of authors, with an index into articles they have
> written. I need to search the authors list for an author's name (via a
> text input), then, once I have that information, I need to hit table2
> and pull out all articles they have written, and table3, which contains
> supplemental information on the articles (which I can do with a join).
> I know I can do a join for the second query, but I don't know how to
> access the results from the second part of the query as search criteria
> using ITL. Does anyone have any examples of this?
>
> In MySQL, I would first
>
> select articleid from nc_authorarticle where authordisplay like
> "%some_author's_name_here%";
>
> This would give me back a list of integers which I would then use in
> the following:
>
> select distinct title, pubdate, from nc_redarticle, nc_authorarticle
> where nc_authorarticle.articleid = nc_redarticle.uniqueID and
> nc_redarticle.uniqueid=__whatever_articleids_were_returned_from_previous
> _search;
>
Your two selects only deal with two tables in total, rather than the
three you suggested. You have said that table1 and table2 both
contain "all articles [the authors] have written" so, assuming that
is not simply a mistake in your question, how about using something
like this:
SELECT r.title, r.pubdate
FROM nc_redarticle r, nc_authorarticle a
WHERE r.uniqueid = a.articleid
AND a.authordisplay LIKE '%author_name_here%'
By the way, I am assuming that uniqueID and uniqueid are the same
column, in the nc_redarticle table.
--
_/ _/ _/_/_/_/ _/ _/ _/_/_/ _/ _/
_/_/_/ _/_/ _/ _/ _/ _/_/ _/ K e v i n W a l s h
_/ _/ _/ _/ _/ _/ _/ _/_/ kevin@cursor.biz
_/ _/ _/_/_/_/ _/ _/_/_/ _/ _/
More information about the interchange-users
mailing list