Lookup and Query.

REPLY TO: CHARLEEN STONER REFERENCE: LOOKUP ROWS AND QUERY. < I'm running INFORMIX-SQL Version 2.10.03F. < I have a screen form that uses a table, info. There's a state column < in info. One of my users wants to see territories (which will depend < on state) on this form. He wants the territory information to reside < in another table, salestate, which has a state column, and three < territory columns. < No problem, right? You just do a lookup join: < [attribute] = info.state = salestate.state, < lookup [ter1] = salestate.territory1 joining *salestate.state, < lookup [ter2] = salestate.territory2 joining *salestate.state, < lookup [ter3] = salestate.territory3 joining *salestate.state; < ...and it looks fine: < Territory WE WE WE < [...portion of form deleted] < City [San Francisco ] State [CA] Zip [94110 ] < No problem, except that this user wants to be able to query on the < territory fields (to get all the records of the appropriate states), < which we all know you can't do with a lookup field. < Is there a way to do something *like* a lookup without actually using < a lookup join? < Sorry if this is a dumb question or if I'm missing something obvious... < Charleen Charleen, ( The ONLY REALLY dumb question, is the question NOT asked because it was thought to be a DUMB QUESTION.) It sounds to me like you need to use a MASTER OF and DETAIL relationship between the two tables you have used for the lookup commnad. The master/detail relationship simplifies cross-table queries, especially when one row of the master table is associated with several rows of the detail table. (Above statement drawn from the Informix Reference Manual for SQL v. 2.1.0) In this manner the user can query the master table for the state in question, obtain the necessary basic information, execute the MASTER/DETAIL command to shift over to the detail table and query the territories fields. Good luck. Michael S. Gordon Foreman, Gyroscope Section Avionics Division * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Michael Gordon Sacramento Army Depot * * Foreman, Gyroscope Section Directorate For Maintenance * * E-Mail: maint72@saad-emh1.army.mil Attn: SDSSA-MAV-320 * * DSN: 839-2367 Michael S. Gordon * * Comm.: (916) 388-2367 Sacramento, CA 96813-5036 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *