CODEWITHSUNDEEP
sqlms-access-2010
vuyy1182
vuyy1182

Reputation: 1676

Sorting specified data in WHERE clause access sql

My below tables are excel sheet data converted as access table using VBA. I'm sorting the data where EID<>7006 or EID<>3456 i.e sorting all column data but not either EID=7006 or EID=3456. But sometimes EID=7006 won't present in the table based on the excel file i convert.

Table1 has EID=7006 and EID=3456

  Description    EID        Basecode        
   -----------    ----       ---------    
  ssdad          3456       S2378797     
  gfd            1002       S1164478     
  gfdsffsdf      1003       R1165778     
  ssdad          3456       M0007867     
  gfd            1005       N7765111     
  gfdsffsdf      7006        W5464111     
  gfd            1005       N7765111

some times Table1 does not have EID=7006 

 Description    EID        Basecode        
   -----------    ----       ---------    
  ssdad          3456       S2378797     
  gfd            1002       S1164478     
  gfdsffsdf      1003       R1165778     
  ssdad          3456       M0007867     
  gfd            1005       N7765111     
  gfdsffsdf       88        W5464111     
  gfd            1005       N7765111

If i specify my query ignoring both 7006 or 3456 like in table1, since i don't know whether EID=7006 present or not, i represent in the query like

   SELECT Description,EID,Basecode from table2 where EID<>7006 or EID<>3456

Still i see 7006 and 3456 in the result query.

Upvotes: 0

Views: 53

Answers (3)

sion_corn
sion_corn

Reputation: 3141

Using OR in a WHERE clause is very tricky. in this case, your OR operator is acting like a UNION. It is behaving as if you had written this: 

SELECT Description,EID,Basecode 
from table2 
where EID<>7006 

union

SELECT Description,EID,Basecode 
from table2 
where EID<>3456

Since each set contains the EID value from the other set, you will continue to see the EID values you are trying to filter out.

What you need to do is write:

SELECT Description,EID,Basecode from table2 where (EID<>7006 and EID<>3456)

or you can phrase it like the following, which will produce the same result as using the logical AND operator:

SELECT Description,EID,Basecode from table2 where EID NOT IN ("7006","3456")

Upvotes: 0

D Stanley
D Stanley

Reputation: 152541

A slightly cleaner query would be:

SELECT Description, EID, Basecode 
FROM table2 
WHERE EID NOT IN (7006, 3456)

Upvotes: 3

willoller
willoller

Reputation: 7330

Looks like your issue is using OR when you mean AND

SELECT Description,EID,Basecode from table2 where EID<>7006 AND EID<>3456

Upvotes: 1

Related Questions