CODEWITHSUNDEEP
sqlsqlite
SSS
SSS

Reputation: 791

SQLITE: Filter out rows across multiple columns

I have the following example table.

1 2 3
A| |B  -- Row 1
 | |A  -- Row 2
 |B|   -- Row 3
 | |   -- Row 4
A|A|A  -- Row 5
C| |C  -- Row 6

My aim is to systematically filter 'out' rows (presumably using != WHERE clauses). I know how to filter 'in' rows using = WHERE clauses but am unsure if it is possible to filter 'out' rows. Please see the following three examples:

1) Filter out A (i.e. leave any rows containing B,C or blank):

--> This should return everything apart from row 5 because every other row contains at least 1 B, C or blank cell

2) Filter out A AND Blanks (i.e. leave any rows containing B or C):

--> This should return rows 1, 3, 6 because each of these rows contains a B or a C

3) Filter out blanks (i.e. leave any rows containing A,B or C):

--> Return rows 1, 2, 3, 5, 6 leaving out Row 4 because this does not include a A, B or a C

A function to generate a query using an = clause would be:

void filterInRows(String colFilter)
{
    List uniqueCols = ['A', 'B', 'C', ''];
    String query = 'SELECT * FROM Test WHERE ';
    int filterCounter = 0;
    uniqueCols.forEach((String col)
   {
       if(col != colFilter)
       {
           query += (filterCounter != 0) ? ' AND ' : ''; 
           query += "One = '$col' OR Two = '$col' OR Three = '$col'";
           filterCounter++;
       }
   });

   query += ';';
}

For example, if we call filterInRows('A') then we get back:

SELECT * FROM Test WHERE One = 'B' OR Two = 'B' OR Three = 'B' AND One = 'C' OR Two = 'C' OR Three = 'C' AND One = '' OR Two = '' OR Three = '';

Which solves the first example above. However, I am stuck in writing a function/query that would allow me to achieve the same thing but filtering rows out rather than in. Queries such as the following fail to achieve this for obvious reasons:

SELECT * FROM Test WHERE One != 'A' OR Two != 'A' OR Three != 'A';
SELECT * FROM Test WHERE One != 'A' AND Two != 'A' AND Three != 'A';

I am using SQLite. The code above is written in Dart but is just an example - happy for counter examples in any other language. My aim is to filter stuff out rather than filter stuff in. I believe you need to do this using != but I am open to another method using different keywords if they exist. Please let me know if I have not explained this problem clearly. Thanks in advance.

Upvotes: 1

Views: 2073

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Use is not:

SELECT *
FROM Test
WHERE One IS NOT 'A' AND Two IS NOT 'A' AND Three IS NOT 'A';

As the documentation explains, this is the NULL-safe operator in SQLite:

The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true).

In ANSI SQL, IS is IS NOT DISTINCT FROM. Some databases use <=>.

Upvotes: 2

Related Questions