Re: CONSTRUCT with multible tables?

->From: zorba@netcom.com (Harry Skelton) ->Subject: CONSTRUCT with multible tables? ->Date: Fri, 7 Jan 1994 14:06:58 GMT ->Reply-To: zorba@netcom.com (Harry Skelton) ->Organization: USS Enterprise -> ...omitted... -> define where_clause char(200) -> construct by name where_clause on -> clnt.clnt_nmbr, -> clnt.clnt_nm_1, -> clnt_contact.contact_nm_1, <<=== may cause problems, see below -> clnt_contact.contact_nm_2, -> clnt.addr_1, -> ... -> -> let sql_var="select * from clnt,clnt_contact where ",where_clause, -> " and clnt_contact.clnt_nmbr = clnt.clnt_nmbr" -> -> This is inconsistant in it's selection. An additional problem is that I -> want to select the client record, and the contact record, but if a -> contact record is not present, I still want the client record anyway. -> ...omitted... ->-- -> Harry Skelton - 1848 Beaver Dam Lane - Marietta, Georgia - 30062 -> 404-590-7100 or 800-366-8181 Work -- 404-578-8085 Home -> skelton@jdp.dragon.com I don't understand what you mean by "inconsistant in it's selection", so I can't help you with that one, but the other is relatively easy. Use an outer join to return client info whether or not there is contact info. Syntax is: select * from clnt, OUTER clnt_contact where ... There are a few limitations on outer joins, one of which affects your example. If you select on fields in "outer-ed" table, then you get REALLY slow response, and sometimes Informix has returned data that I did not expect. Hmm, I wonder if that is related to your inconsistent problem. Anyway, I have had good luck with outer joins when I want ALL the contact info for clients, and client info even if no contacts. Asking for only some of the data on the "outer-ed" contact side is what seems to cause the problems. This may be due to the three value logic that is used with nulls not doing what I expected, since the outer join returns nulls for columns when no record exists on that side of the join. Did you save a copy of the null logic thread that was in this forum a while ago? Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\