Re: easy question
On Mon, 27 Sep 2004 11:02:22 -0400, Rein Puksand wrote: The EXISTS subquery method is one, here's another: select table1.* from table1 left outer join table2 on table1.number = table2.number and table1.lname = table2.lname where table2.lname IS NULL and table2.number IS NO NULL; Art S. Kagel > 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; =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