SQL Query Selection Syntax-CodePudding

Lets say I have a table with columns Customer and job.

I have rows:

Customer 1, Job 1
Customer 1, Job 2
Customer 1, Job 3
Customer 2, Job 1
Customer 2, Job 2
Customer 2, Job 3
Customer 3, Job 1
Customer 3, Job 2
Customer 3, Job 3

I have a query:

Select *
from Table
Where Customer <> 'Customer 1'
  AND (Customer <> 'Customer 2' AND Job <> 'Job 1')

The rows I would want returned from this are:

Customer 2, Job 2
Customer 2, Job 3
Customer 3, Job 1
Customer 3, Job 2
Customer 3, Job 3

But instead the rows returned are only for Customer 3 jobs 2 and 3. I want to still include Customer 2 with jobs 2 and 3 and Customer 3 Job 1.

There are a lot more columns than this in the database but these where the ones in question so I only included these. The rows actually store ticket data in them. I want the customer to be able to filter out what tickets show up in reports and they may want to exclude a customer and also exclude a job from another customer but include everything else. Customer names will be unique but job names can be duplicated across customers.

CodePudding user response：

You can set individual Customer <-> Job exclusion clauses in your where, and then just do a finally not in excluding anyone you either don't want or have an existing clause for:

select *
from Table
where (Customer = 'Customer 2' and Job <> 'Job 1')
    or Customer not in ('Customer 1', 'Customer 2')

For example of extending this, if you had a Customer 4 and wanted to exclude Job 2 for them:

select *
from tbl
where (Customer = 'Customer 2' and Job <> 'Job 1')
    or (customer = 'Customer 4' and Job <> 'Job 2') --add specific customer and job exclusion
    or Customer not in ('Customer 1', 'Customer 2', 'Customer 4') --add customer 4 here because we have a specific clause for them