CODEWITHSUNDEEP
mysqlsqlsqlite
MichaelTaylor3D
MichaelTaylor3D

Reputation: 1665

mySQL/SQlite subtractive query

I need a query in which it starts off by selecting the entire table, then there would be a few more querys that would remove entries from the first query. Ive accomplished this by using several querys and then comparing the results in my application. I was wondering if I can accomplish this in a single query.

Algorithm

  1. Select All AccountIDs from table

  2. Select AccountIDs from table where parameter1 = true

  3. Remove those matches from the original query result

  4. Select AccountIDs from table where parameter2 = true

  5. Remove those matches from the remaining query result

  6. and so on up to N parameters.

This would need to also be compatible with both mySQL and SQLite

Upvotes: 1

Views: 183

Answers (2)

RandomSeed
RandomSeed

Reputation: 29769

I think you are looking for this:

SELECT AccountID FROM the_table
LEFT JOIN (
    SELECT AccountID FROM the_table
    WHERE
        parameter1 = true OR
        ... OR
        parameterN = true ;
) AS not_included USING (AccountID)
WHERE not_included.AccountID IS NULL -- only items with no match in the "not_included" sub-query

The not_included subquery returns all items for which any parameter is set to TRUE. You actually want to exclude these records from your final result set.

Then LEFT-JOIN the_table (i.e. all items) to this sub-result. The WHERE...IS NULL clause excludes items present in the_table but not present in not_included.

Therefore only items which you do not want to exclude remain in the final result set.

Upvotes: 2

CL.
CL.

Reputation: 180172

The most direct way to implement your algorithm is to use a compound SELECT statement:

SELECT AccountID FROM MyTable
EXCEPT
SELECT AccountID FROM MyTable WHERE parameter1 = 1
EXCEPT
SELECT AccountID FROM MyTable WHERE parameter2 = 1

However, this is also possible with a single WHERE expression:

SELECT AccountID
FROM MyTable
WHERE NOT (parameter1 = 1 OR
           parameter2 = 1 OR
           ...)

Upvotes: 2

Related Questions