There are 2 tables: users and props. You can see here . I do a sample of ID from users whose property = 31 and value = 'Y' This query will display 1, 5 :

 SELECT DISTINCT U.id AS ID FROM users U INNER JOIN props UP ON U.id = UP.id_user WHERE (U.country = 20) AND (UP.property=31 AND UP.value='Y')

You need to do a reverse request, i.e. those records that do not have this UP.property=31 . T e should output 3, 6 instead of all (except country = 30 )

Schema and data scripts:

 CREATE TABLE users (`id` int, `name` varchar(15), `country` int) ; CREATE TABLE props (`id` int, `property` int, `id_user` int, `value` varchar(2)) ; INSERT INTO users (`id`, `name`, `country`) VALUES (1, 'vadim', 20), (2, 'petya', 20), (3, 'vasya', 20), (4, 'serega', 30), (5, 'lesha', 20), (6, 'vitya', 20) ; INSERT INTO props (`id`, `property`, `id_user`, `value`) VALUES (1, 31, 1, 'Y'), (2, 33, 1, 'Y'), (3, 36, 1, 'Y'), (4, 31, 2, 'N'), (5, 33, 2, 'Y'), (6, 35, 3, 'N'), (7, 32, 3, 'Y'), (8, 30, 4, 'Y'), (9, 32, 4, 'Y'), (10, 31, 5, 'Y'), (11, 34, 5, 'Y'), (12, 36, 6, 'Y'), (13, 35, 6, 'Y') ;
  • what does "no altogether" mean? not equal to 31 or zero? - Vartlok
  • "Not at all", so in the props table for this user there is no property = 31 - vgedich
  • Describe in more detail why the conditions are country = 20 and value = 'Y' - in the context of your problem, they seem superfluous, i.e. the question can be asked more succinctly ... "Reverse request" is not a problem statement. Rephrase, and then in the answers we are already trying to read your thoughts. - pegoopik
  • Value has only value. It is necessary to add one more condition to what @Denis wrote: property = 31 and value <> 'Y' - vgedich
  • one
    "except country = 30" is incorrect. In addition to the values ​​of 20 and 30 there may be other values ​​of country, reformulate the question in a more correct form. Of course, in the text of the question, not in the comments. So that other users on your question could find the answer to their question. - pegoopik

2 answers 2

Try this:

 SELECT DISTINCT u.id as id FROM users u WHERE u.country = 20 AND u.id NOT IN (SELECT p.id_user FROM props p WHERE p.property = '31')

In the subquery we get all users who have property = '31' , as a result, we eliminate them.

Based on the addition of the vehicle from the comments:

 SELECT DISTINCT u.id as id FROM users u WHERE u.country = 20 AND u.id NOT IN (SELECT p.id_user FROM props p WHERE p.property = '31') UNION SELECT DISTINCT U.id AS ID FROM users U INNER JOIN props UP ON U.id = UP.id_user WHERE (U.country = 20) AND (UP.property=31 AND UP.value<>'Y')

Select the records that do not have property = 31 in props, combine them with the query, which has property = 31 and value <> 'Y' in props property = 31 and value <> 'Y'

  • In your request, user 2 is also displayed, which has property 31 in the props table. And by condition, everything is without 31. - Andrii Kulyk
  • @AndriiKulyk note the line Based on the addition of the vehicle from the comments , for the presence of 2 requests in my answer and read, please, comments to the question. - Denis
  • Yes, I once again looked there there was a statement - “No at all”, then in the props table there is no property = 31 for this user - the second user has property 31. - Andrii Kulyk
  • @AndriiKulyk I will advise nevertheless to read the comments more carefully and see the editing of my answer. Further dispute is meaningless, you did not understand what the author wanted. - Denis

So displays 3 and 6:

 SELECT DISTINCT U.id FROM users U WHERE (U.country = 20) AND 31 not in (SELECT P.property FROM props P WHERE P.id_user = U.id);

In the subquery, we select the entire property for the current user, and if he does not have property 31, then we select it.