Richard.... More efficient this way if there ate multiple instance records in the compno field
Select columns from table Where compno not in (select distinct compno from table Where compno='value1' or compno='value2' or... Group by compno) Dave -----Original Message----- From: ProFox [mailto:[email protected]] On Behalf Of Richard Kaye Sent: 16 April 2014 16:19 To: [email protected] Subject: RE: SQL Query You don't say how many values you want to exclude. If there are only a few you can do something like: Select columns from table Where compno not in ('value1','value2',...) Or Select columns from table Where compno not in (select compno from table Where compno='value1' or compno='value2' or... Group by compno) Or if this is VFP SQL you can also use the INLIST function in the where clause like this: WHERE NOT INLIST(compno, 'value1','value2',...) I'm not sure if using the NOT with INLIST is Rushmore optimizable. I'm pretty sure that prior to VFP9, NOT was not optimizable. It may have changed. And INLIST only supports 26(?) values -- rk -----Original Message----- From: ProfoxTech [mailto:[email protected]] On Behalf Of Andrew Stirling Sent: Wednesday, April 16, 2014 11:11 AM To: [email protected] Subject: SQL Query Hi I have a table with a column compno which has Comp1 Comp2 Comp3 etc I have got a string, tempexclude with 'Comp2,Comp4,Comp99' in it. How do I get a SELECT to exclude the string values? Thanks Andrew Stirling [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
