Re: Query Help!!

>From: uunet!cbnewsb.cb.att.com!nll (neal.l.leitner) >Message-Id: <1992Apr27.193059.16159@cbfsb.cb.att.com> >Subject: Query Help!! >Date: 27 Apr 92 19:30:59 GMT >X-Informix-List-Id: <news.1108> > >I have a book inventory system where people can log in and look up books >by titles. The Informix table is structured in the following way: > >TABLE xyz (text CHAR(10), file_no INTEGER); > >One solution is: > select file_no from xyz where text = "MURDER" and file_no in > (select file_no from xyz where text = "MYSTERY")> >The other solution was: > select file_no from xyz where text = "MURDER" INTO TEMP T1; > select file_no from xyz where text = "MYSTERY" INTO TEMP T2; > select t1.file_no from t1,t2 where t1.file_no=t2.file_no;> >Does anyone have a alternative solution to make these queries go faster? I haven't checked on the speed, but you can try: SELECT A.File_no FROM Xyz A, Xyz B WHERE A.File_no = B.File_no AND A.Text = "MYSTERY" AND B.Text = "MURDER" This uses the features called table aliassing and self-joins. The extension to three categories is, I trust, obvious. It is relatively easily done in a programming language (ESQL or I4GL) and non-trivial otherwise. BTW: I see you are using Standard Engine: the word TEXT is the data type for a TEXT BLOB in Online -- you would be better off using some other word for the column name if you think you might be using OnLine at any future time. (And, to the afficionado's, I do know that you can get away with CREATE TABLE TABLE in sufficiently recent versions of Informix -- I just don't think you should try!) Yours, Jonathan Leffler (johnl@obelix.informix.com)