easy question

Posted in 2004

Rein Puksand — Mon, 27 Sep 2004 18:02:22 +0300 — source: Usenet: comp.databases.informix

This is a multi-part message in MIME format. ------=_NextPart_000_0013_01C4A4BC.23182B90 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, Easy question! Two tables table1 (number, lname) and table2(number,lname). I want to find elements (lname) from table1, only that not exist in table2 but values in number fields are equal? table1.number = table2.number and table1.lname not in table2.lname Example: Table1 1 Marc 2 Edy 1 hex 4 Mary 2 Sed Table2 1 hex 1 Sed 4 Mary 1 Marc 2 troy and result must be: 2 Edy, 2 Sed What is sql? very thanks, Rein ------=_NextPart_000_0013_01C4A4BC.23182B90 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Dus-ascii"> <META content=3D"MSHTML 6.00.2800.1458" name=3DGENERATOR></HEAD> <BODY> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D671223114-27092004>Hello,</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D671223114-27092004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D671223114-27092004>Easy=20 question!</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D671223114-27092004>Two = tables table1=20 (number, lname) and table2(number,lname).</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D671223114-27092004>I want = to find=20 elements (lname) from table1, only that not exist in = table2 but values=20 in number fields are equal?</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D671223114-27092004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN = class=3D671223114-27092004>table1.number =3D=20 table2.number and table1.lname not in table2.lname </SPAN></FONT><FONT=20 face=3DArial size=3D2><SPAN = class=3D671223114-27092004></SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D671223114-27092004>Example:</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D671223114-27092004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D671223114-27092004>Table1</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D671223114-27092004>       &nbs= p;           &nbsp= ;            =             &= nbsp;     =20 </SPAN></FONT><FONT face=3DArial size=3D2><SPAN=20 class=3D671223114-27092004></SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D671223114-27092004>1=20 Marc</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D671223114-27092004>2=20 Edy</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D671223114-27092004>1=20 hex</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D671223114-27092004>4=20 Mary</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D671223114-27092004>2=20 Sed</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D671223114-27092004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D671223114-27092004>Table2</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D671223114-27092004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D671223114-27092004>1=20 hex</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D671223114-27092004>1=20 Sed</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D671223114-27092004>4 Mary = </SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D671223114-27092004>1=20 Marc</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D671223114-27092004>2=20 troy</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D671223114-27092004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D671223114-27092004>and = result must=20 be: 2  Edy, 2 Sed </SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D671223114-27092004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D671223114-27092004>What = is=20 sql?</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D671223114-27092004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D671223114-27092004>very=20 thanks,</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D671223114-27092004>Rein</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D671223114-27092004></SPAN></FONT> </DIV></BODY></HTML> ------=_NextPart_000_0013_01C4A4BC.23182B90-- sending to informix-list

↪ replying to Rein Puksand

June C. Hunt — Mon, 27 Sep 2004 13:39:23 -0400 — source: Usenet: comp.databases.informix

Rein Puksand wrote: > > This is a multi-part message in MIME format. > Please don't post MIME. > > Easy question! > Two tables table1 (number, lname) and table2(number,lname). > I want to find elements (lname) from table1, only that not exist in table2 > but values in number fields are equal? > > table1.number = table2.number and table1.lname not in table2.lname > Example: > > Table1 > > 1 Marc > 2 Edy > 1 hex > 4 Mary > 2 Sed > > Table2 > > 1 hex > 1 Sed > 4 Mary > 1 Marc > 2 troy > > and result must be: 2 Edy, 2 Sed > > What is sql? SELECT * FROM table1 t1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table2.number = t1.number AND table2.lname = t1.lname); This statement returned the two entries that you requested based on your sample data. -- June Hunt