Path: utzoo!censor!geac!torsqnt!news-server.csri.toronto.edu!cs.utexas.edu!rice!uupsi!sunic!dkuug!freja.diku.dk!rimfaxe.diku.dk!elgaard
From: elgaard@rimfaxe.diku.dk (Niels Elgaard Larsen)
Newsgroups: comp.databases
Subject: Re: sql*forms & multi-table queries
Message-ID: <1990Apr9.102922.28704@diku.dk>
Date: 9 Apr 90 10:29:22 GMT
References: <LAWHORN.90Apr6125922@optis31.uucp>
Sender: news@diku.dk (The Netnews System)
Distribution: comp
Organization: Department Of Computer Science, University Of Copenhagen
Lines: 48

lawhorn@optis31.uucp (Jeff Lawhorn) writes:

>I hope someone out there can help me...

>I have come across a situation where I need to be able to query
>on more than one table at the same time from within a form.  For
>example, given the following form:

>+--------------------------------------------------------+
>|+-----------------------CUST_INFO----------------------+|
>|| Name:_______________________     ORDER DATE:__/__/__ ||
>|| Addr:___________________________  SHIP DATE:__/__/__ ||
>||      ___________________________                     ||
>|+------------------------------------------------------+|
>|+-----------------------ORDER_INFO---------------------+|
>|| ITEM  DESCRIPTION         QTY  UNIT COST  COST       ||
>|| ____  __________________  ___  __,___.__  ___,___.__ ||
>|| ____  __________________  ___  __,___.__  ___,___.__ ||
>|| ____  __________________  ___  __,___.__  ___,___.__ ||
>|| ____  __________________  ___  __,___.__  ___,___.__ ||
>|| ____  __________________  ___  __,___.__  ___,___.__ ||
>|+------------------------------------------------------+|
>+--------------------------------------------------------+

>I need to be able to do a query based on both the ship date and
>the item.  These are stored in 2 different tables (cust_info &
>order_info), and so far I have not been able to figure out how to
>do this query.  It is a simple join from sql, but the people that
>are going to be using this form have little to no knowlege of
>sql. 

If you only need a query, just make a view in sql and 
then a make a form over the view.

If you want to make updates, you can put a non-database, non-enterable 
field ITEMD in CUST-INFO. Then put 
   "WHERE ITEMD in (SELECT ITEM# from ORDER_INFO
      WHERE DESCRIPTION = :ITEMD)" 
in the ORDER BY clause 





            Niels Elgaard Larsen            | This signature
            Institute of Datalogy,          | contains 2 error
            University of Copenhagen        |
            E-mail: elgaard@freja.diku.dk   |