[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