Hello! I am using MySQL 5.

In the table I have the following lines

column1 --- column2 1 --- 2 1 --- 3 2 --- 2

I have 2 input lists:

  1. A list of "positive" column2 values.
  2. A list of "negative" column2 values.

It is required to display all column1, which (in the whole table) have a "connection" (are in one line-entry in the table) with some positive column2. And while there is not a single line in the table, where there is a column1 and some negative column2.

For example, the positive list is “2”, the negative one is “3.”

With this approach, there should be only one line in the sample.

 2 --- 2

I tried to write a request and that's what came up:

 SELECT * FROM table WHERE column2 IN (2) AND WHERE column2 NOT IN (3);

But I get at the exit 2 lines:

 1 --- 2 2 --- 10
  • Nitsche is not clear - Ale_x
  • I do not understand? where column2 = 2; and all? - Gorets
  • Changed the wording of the question. Added the "Required" section to make sense. - kezman 6:39

2 answers 2

Second WHERE extra:

 SELECT * FROM table WHERE column2 IN (2) AND column2 NOT IN (3);

With this approach, there should be only one line in the sample 2 --- 10

With this approach there will be one line: 1 --- 2

Possible variant:

 SELECT * FROM table WHERE column2 IN (2) AND column1 NOT IN (SELECT DISTINCT column1 FROM table WHERE column2 IN (3));
  • Yes, litter. I corrected the wording of the question. - kezman
  • This worked like a charm! Thank you, sir! - kezman

It seems to me that the task was set incorrectly. If you have both lists restricted by column2 , then you need to remove all negative values ​​from the list of positive values, and then make a query only on the cleared list of positive values.

  • inso rightly answered - kezman
  • Imagine the situation. You have 2 lines in the table. One with a positive column2, the other with a negative column2. And there, and there column1 matches. You don’t need to get it because of "And yet there is NO ONE line in the table with column1 and some negative column2.". And if you take the "cleaned" list - this line will get from the database. - kezman
  • What I propose is similar to the first request from the inso answer, it will only work faster. From the wording it is not clear how to understand "where is column1". What is meant by checking for null? - Modus
  • The correct query is this: SELECT * FROM table WHERE column2 IN (2) AND column1 NOT IN (SELECT DISTINCT column1 FROM table WHERE column2 IN (3)); - kezman
  • Oh, then yes. - Modus