Return results with several LIKE and NOT LIKE requirements

Question

I am trying to create a query that selects only a set of values, but excludes other ones.

The column I am selecting it's called "Participants" and the values therein are not a single text item, but a list of items separated by commas (length and number of items vary), for example: "Alpha, Delta, Eta" or the value: "Beta, Gamma or the value: "Delta, Beta, Epsilon, Theta, Vau".

I wrote this query to try and obtain results that will only include value like Delta, Eta, Epsilon and Vau but not Alpha, Beta, Gamma (it means that I don't want the value: "Alpha, Delta, Eta" to be included, but I want values like: "Delta, Zeta, Epsilon")

SELECT DISTINCT Name, Category, CODE, Participants
WHERE(Participants NOT LIKE '%Alpha%'
      OR Participants NOT LIKE '%Beta%'
      OR Participants NOT LIKE '%Gamma%')
     AND (Participants LIKE '%Delta%'
          OR Participants LIKE '%Eta%'
          OR Participants LIKE '%Epsilon%'
          OR Participants LIKE '%Vau%');

The results include all the values specified in the LIKE part of the query, but NOT LIKE doesn't seem to work as I keep getting results like: "Alpha, Eta, Epsilon.

I could create multiple queries in Access to filter with the LIKE query and then with the NOT LIKE, but I would like to find a way to do it in a single query.

Online I could only find examples where there is a single LIKE and a NOT LIKE in the query

Can anybody suggest a solution please?

Answer 1

In your query when evaluating NOT LIKE you should use AND instead of OR, because you want all of them to be met.

SELECT DISTINCT
    Name,
    Category,
    CODE,
    Participants
WHERE (Participants NOT LIKE '%Alpha%'
      AND Participants NOT LIKE '%Beta%'
      AND Participants NOT LIKE '%Gamma%')
     AND (Participants LIKE '%Delta%'
          OR Participants LIKE '%Eta%'
          OR Participants LIKE '%Epsilon%'
          OR Participants LIKE '%Vau%');

This is in contrast to those later conditions where you want any of the values be LIKE values selected where AND is used.

Alternatively you can rephrase as:

WHERE NOT
      (Participants LIKE '%Alpha%'
    OR Participants LIKE '%Beta%'
    OR Participants LIKE '%Gamma%'
      )
  AND (Participants LIKE '%Delta%'
    OR Participants LIKE '%Eta%'
    OR Participants LIKE '%Epsilon%'
    OR Participants LIKE '%Vau%'
      );

Answer 2

If you can put these conditions into table variables or Table Valued Parameters, the query becomes somewhat simpler:

DECLARE @ExclFilters TABLE (Value varchar(100);
INSERT @ExclFilters (Value)
VALUES
  ('Alpha'),
  ('Beta'),
  ('Gamma');
DECLARE @InclFilters TABLE (Value varchar(100);
INSERT @InclFilters (Value)
VALUES
  ('Delta'),
  ('Eta'),
  ('Epsilon'),
  ('Vau');


SELECT DISTINCT
    Name,
    Category,
    CODE,
    Participants
FROM whatever t
WHERE NOT EXISTS (SELECT 1
    FROM @ExclFilters f
    WHERE t.Participants LIKE '%' + Value + '%')
  AND EXISTS (SELECT 1
    FROM @InclFilters f
    WHERE t.Participants LIKE '%' + Value + '%');