Help with Query
in [Programming SQL]
|
From: Sam. Commar on 26 May 2010 11:33 I want to run a query to exclude all Projects from the pjproj table EXCEPT those starting with and L, C, 08, 09, 10, 11, 01PM and 05PM I tried something like below but that did not work. select project from pjproj where project not in ('L%', 'C%', '08%', '09%', '10%', '11%', '01PM%', '05PM%') Id appreciate if someone can help me with the query Thanks Sam
From: Victor Schrader on 26 May 2010 13:06 try something like this: select p.* from pjproj p where not exists ( select j.* from pjproj j where j.project = p.project and j.project in ('L%', 'C%', '08%', '09%', '10%', '11%', '01PM%', '05PM%')) --- frmsrcurl: http://msgroups.net/microsoft.public.sqlserver.programming/Help-with-Query,3
From: Erland Sommarskog on 26 May 2010 17:34 Sam. Commar (s_commar(a)hotmail.com) writes: > I want to run a query to exclude all Projects from the pjproj table > EXCEPT those starting with and L, C, 08, 09, 10, 11, 01PM and 05PM > > I tried something like below but that did not work. > > select project from pjproj where project not in > ('L%', 'C%', '08%', '09%', '10%', '11%', '01PM%', '05PM%') > You need to write: project NOT LIKE 'L%' AND project NOT LIKE 'C%' AND ... You could also store 'L', 'C', '08' etc in table and then say: SELECT pj.project FROM pjproj pj WHERE NOT EXISTS (SELECT * FROM skipthese s WHERE pj.project NOT LIKE s.str + '%') -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Tom Cooper on 26 May 2010 17:40 Either select project from pjproj where left(project, 1) Not In ('L', 'C') and left(project, 2) Not In ('08', '09', '10', '11') and left(project, 4) Not In ('01PM', '05PM') or select project from pjproj where project Not Like 'L%' and project Not Like 'C%' and project Not Like '08%' and project Not Like '09%' and project Not Like '10%' and project Not Like '11%' and project Not Like '01PM%' and project Not Like '05PM%' Tom "Sam. Commar" <s_commar(a)hotmail.com> wrote in message news:C164A08A-F692-45CE-B69E-00CC0820F97E(a)microsoft.com... >I want to run a query to exclude all Projects from the pjproj table EXCEPT >those starting with and L, C, 08, 09, 10, 11, 01PM and 05PM > > I tried something like below but that did not work. > > select project from pjproj where project not in > ('L%', 'C%', '08%', '09%', '10%', '11%', '01PM%', '05PM%') > > > Id appreciate if someone can help me with the query > > > Thanks > > Sam
From: --CELKO-- on 26 May 2010 19:17
On May 26, 10:33 am, "Sam. Commar" <s_com...(a)hotmail.com> wrote: > I want to run a query to exclude all Projects from the pjproj table > EXCEPT those starting with and L, C, 08, 09, 10, 11, 01PM and 05PM > > I tried something like below but that did not work. > > select project from pjproj where project not in > ('L%', 'C%', '08%', '09%', '10%', '11%', '01PM%', '05PM%') > > Id appreciate if someone can help me with the query > > Thanks > > Sam Another weird way to do this: SELECT project_name FROM PjProj WHERE project_name = CASE WHEN project_name LIKE 'L%' THEN 'F' WHEN project_name LIKE 'C%' THEN 'F' WHEN project_name LIKE '08%' THEN 'F' WHEN project_name LIKE '09%' THEN 'F' WHEN project_name LIKE '10%' THEN 'F' WHEN project_name LIKE '11%' THEN 'F' WHEN project_name LIKE '01PM%' THEN 'F' WHEN project_name LIKE '05PM%' THEN 'F' ELSE 'T' END = 'T' ); Sort the WHEN clause by likelihood and you might get a performance boost. |