1

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?

1
  • The condition Participants NOT LIKE'%Alpha%' OR Participants NOT LIKE'%Beta%' OR Participants NOT LIKE'%Gamma%' looks wrong.
    – mustaccio
    Jul 19 at 13:59
2

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%'
      );
2
  • thank you, your solution did work for me. I didn't realise I had to use the AND with the multiple NOT LIKE. Jul 20 at 10:37
  • @Geo_explorer: That's one of De Morgan's Laws for you. When you want either, you say, A or B. The opposite – neither – would be not (A or B) → (not A) and (not B). It actually makes sense on its own, without fancy names, though perhaps not immediately obvious. "I do not want either this or that = I do not want this and I do not want that"
    – Andriy M
    Jul 27 at 15:21
0

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 + '%');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.