CODEWITHSUNDEEP
sqlms-access
vuyy1182
vuyy1182

Reputation: 1686

How to retrieve column data other than specified in 2 sql queries

I have a table (tbl1) like 

 Description    EID        Basecode        
   -----------    ----       ---------    
  ssdad          1001       S2378797     
  gfd            1002       S1164478     
  gfdsffsdf      1003       R1165778     
  ssdad          1004       M0007867     
  gfd            1005       N7765111     
  gfdsffsdf      1006       W5464111     
  gfd            1005       N7765111     
  gfdsffsdf      1006       A4000011 
  gfdsffsdf      1006       W5464111     
  ssdad          1001       2378797     
  gfd            1002       1164478  
  ssdad          1001       965000
  gfd            1002       780000
  yjgk           4456       540000
  kjhkh          2009       150000
  ddd            1004       1040
  d88jg          1004       14C676
   fsa           6565       158 
   fdh           1004       2Khlm
   ggdg          2009       967

I'm retrieving all Basecode column data starts with only letters other than 'W', 'N' by this query

 SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 WHERE Not 
 IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N");

And retrieving all Basecode if column data length >6 and with numbers '96', '78','54','15' by this query 

SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 
WHERE (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR 
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR 
(((Len([Basecode]))>6) AND  ((Left([Basecode],2))='96'));

How do i get other data which won't retrieve based on above queries, other than data mentioned on these queries like this

Description       EID        Basecode        
   -----------    ----       ---------    

  ssdad          1001       2378797     
  gfd            1002       1164478  
  ddd            1004       1040
  d88jg          1004       14C676
  fsa            6565       158 
  fdh            1004       2Khlm
   ggdg          2009       967

Third query not working 

SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 
WHERE (IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N"))
AND NOT (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR 
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR 
(((Len([Basecode]))>6) AND  ((Left([Basecode],2))='96'));

Upvotes: 0

Views: 77

Answers (1)

Hogan
Hogan

Reputation: 70531

Something like this:

SELECT DISTINCT tbl1.EID,tbl1.Description,tabl1.Basecode 
FROM tbl1 
WHERE Basecode NOT IN
(
  SELECT tabl1.Basecode 
  FROM tbl1 
  WHERE Not IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N");
  UNION
  SELECT tabl1.Basecode
  FROM tbl1 
  WHERE Len([Basecode])>6 AND  Left([Basecode],2) IN ('15','54','78','96')
)

This would also work

SELECT DISTINCT tbl1.EID,tbl1.Description,tabl1.Basecode 
FROM tbl1 
WHERE 
  Basecode NOT IN
  (
    SELECT tabl1.Basecode 
    FROM tbl1 
    WHERE Not IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N");
  ) T1 AND 
  Basecode NOT IN
  (
    SELECT tabl1.Basecode
    FROM tbl1 
    WHERE Len([Basecode])>6 AND  Left([Basecode],2) IN ('15','54','78','96')
  ) T2

Upvotes: 1

Related Questions