Vfrdtyky

I have two tables Person and _Person_Name which contains a column Name and a column Owner with Person.Id as foreign key. I'm looking for two search operations I'd call EVERY and NO.

1.) Every

The following returns only the Person IDs for which all corresponding names match for query LIKE '%n%':

SELECT Person.Id as Result FROM Person

INNER JOIN _Person_Name AS __T1 ON Person.Id = __T1.Owner 

WHERE __T1.Name LIKE '%n%'

GROUP BY Result

HAVING Count(Result)=(Select Count(*) FROM _Person_Name 

  WHERE Person.Id=_Person_Name.Owner)

But the problem is that I also have to deal with other queries for which just a single match suffices, and the HAVING clause applies to all terms in the WHERE clause.

Is there a way to get the same effect as in the HAVING clause in the above query, but somehow express this within the WHERE clause such that other, ordinary conditions can be added to it?

Example:

SELECT Person.Id as Result FROM Person 

INNER JOIN _Person_Name AS __T1 ON Person.Id = __T1.Owner

WHERE (EVERY __T1.Name LIKE '%n%') 

OR (__T1.Name LIKE 'John')

The second disjunct should just behave in the ordinary way without any restrictions. Now I'm looking for a way to express EVERY like in the above HAVING clause. The query should return __T1.Owner (=Person.Id) whenever it is the case that one field with matching owner has a name field __T1.Name LIKE 'John' or it is the case that all __T1.Name fields with matching owner contain an 'n'.

If it is not possible to express this in the WHERE clause, how can it be expressed?

2.) No

The NO search operation is just like EVERY but the base condition is negated, i.e., I'm looking for the Persons for which none of their associated Name parts matches the query. I suppose I can get that easily if I have a solution for EVERY.

EDIT: The answer by Dávid Laczkó is correct. If he hasn't edited his reply already, a small change is required. Here is the solution for NO based on his answer:

SELECT DISTINCT Person.Id as Result FROM Person 

INNER JOIN _Person_Name AS __T1 ON Person.Id = __T1.Owner

WHERE

    NOT EXISTS (

        SELECT 1 FROM _Person_Name AS __T2

        WHERE __T2.Name LIKE '%n%' AND Person.Id=__T2.Owner

        )

Let me start with an idea for NO: you can check if no row exists if you look up by condition LIKE '%n%'. You can do it in the WHERE clause with EXISTS subquery:

WHERE

    NOT EXISTS (

        SELECT 1 FROM _Person_Name AS __T2 WHERE Person.Id = __T2.Owner

        and __T2.Name LIKE '%n%'

        )

OR (__T1.Name LIKE 'John')

NOT EXISTS checks if there is NO match.

Based on this the implementation for EVERY is to see if the negation of your condition for no row is true, so you are looking for NOT LIKE '%n%':

WHERE

    NOT EXISTS (

        SELECT 1 FROM _Person_Name AS __T2 WHERE Person.Id = __T2.Owner

        and __T2.Name NOT LIKE '%n%'

        )

OR (__T1.Name LIKE 'John')

This time NOT EXISTS will make sure no rows found that does NOT match your criteria - therefore EVERY row matched it.