CODEWITHSUNDEEP
sqlsql-server
AJF
AJF

Reputation: 1931

Complex SQL Query Filter

I have a table named CompApps and I wish to retrieve particular records based on a multiple condition query. My SQL is very rusty and this is why I am asking on Stack Overflow. What I need is to amend the SQL below to include a where clause that will exclude records that do not have any relevant information in the fields Interface, ExAPI, ExtraInfo, OpenCol. That is, in the image of the current query results below I want rows 170, 173, 174, 175, 177, 182, 185, 190 and NOT rows that only have the value of None, N\A or an empty value in Interface, ExAPI, ExtraInfo, OpenCol 

SELECT RefNum, Interface, ExAPI, ExtraInfo, OpenCol
FROM CompApps

enter image description here

Upvotes: 2

Views: 146

Answers (4)

Asif Mehmood
Asif Mehmood

Reputation: 984

Try this:

SELECT 
    RefNum, Interface, ExAPI, ExtraInfo, OpenCol
FROM 
    CompApps
WHERE 
    (Interface IS NOT NULL AND (Interface !='None' AND Interface !='N/A')) 
OR 
    (ExAPI IS NOT NULL AND (ExAPI != 'None' AND  ExAPI != 'N/A')) 
OR 
    (ExtraInfo IS NOT NULL AND (ExtraInfo != 'None' AND  ExtraInfo != 'N/A')) 
OR 
    (OpenCol IS NOT NULL AND (OpenCol != 'None' AND OpenCol != 'N/A'))

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Or maybe like this

Correcting this according to dnoeth's comment:

SELECT RefNum, Interface, ExAPI, ExtraInfo, OpenCol
FROM CompApps
WHERE Interface NOT IN('None','N/A')
   OR ExAPI NOT IN('None','N/A')
   OR ExtraInfo NOT IN('None','N/A')
   OR OpenCol NOT IN('None','N/A');

Upvotes: 3

Abdul Rasheed
Abdul Rasheed

Reputation: 6719

Try this

SELECT RefNum, Interface, ExAPI, ExtraInfo, OpenCol
FROM CompApps
WHERE (Interface NOT IN ('','None','N/A'))
   OR (ExAPI NOT IN ('','None','N/A'))
   OR (ExtraInfo NOT IN ('','None','N/A'))
   OR (OpenCol NOT IN ('','None','N/A'))

Upvotes: 0

Nino
Nino

Reputation: 7095

where 
(
    (ISNULL(ExAPI, '') <> '' OR ExAPI <> 'N/A' OR ExAPI <> 'None')
    OR
    (ISNULL(ExtraInfo, '') <> '' OR ExtraInfo<> 'N/A' OR ExtraInfo<> 'None')
    OR
    (ISNULL(OpenCol, '') <> '' OR OpenCol<> 'N/A' OR OpenCol<> 'None')
)

Upvotes: 0

Related Questions