How to exclude more rows by one match

Well, sorry but I don’t know how to formulate the subject correctly. That’s why I’d better describe the issue.

I have a kind of following table:

CREATE TABLE IF NOT EXISTS `Test` (
  `Id` INT NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(5) NOT NULL,
  `v1` INT NOT NULL,
  PRIMARY KEY (`Id`)
);

INSERT INTO `Test` (`Name`, `v1`) 
  VALUES ('A', 4), ('A', 3), ('B', 6), ('C', 1), ('C', 2), 
         ('C', 7), ('D', 7), ('D', 5), ('E', 3), ('F', 1), 
         ('F', 2), ('G', 7), ('H', 9), ('H', 1), ('I', 5);

What I need is to find all names that matches e.g. 7. These are 'C', 'D' and 'G'.

And now I need to select all rows except 'C', 'D' and 'G'.
Resulting table is supposed to be:

===============
| Name  | v1  |
|–––––––––––––|
|   A   |  4  |
|   A   |  3  |
|   B   |  6  |
|   E   |  3  |
|   F   |  1  |
|   F   |  2  |
|   H   |  9  |
|   H   |  1  |
|   I   |  5  |
|–––––––––––––|

I’ve tried the query:

select t1.Name, t1.v1
from `Test` as t1
join (
  select Name from `Test`
  where v1 = 7
  ) as t2
  on t1.Name != t2.Name

but only realized that I still do not understand the mechanics of JOIN statements.

Hope for some help with this issue.
Thanks in advance.

Answer

This query:

SELECT Name FROM Test WHERE v1 = 7

returns all the Names that you want to exclude.
Use it with the operator NOT IN:

SELECT * 
FROM Test
WHERE Name NOT IN (SELECT Name FROM Test WHERE v1 = 7)

See the demo.
Results:

> Id | Name | v1
> -: | :--- | -:
>  1 | A    |  4
>  2 | A    |  3
>  3 | B    |  6
>  9 | E    |  3
> 10 | F    |  1
> 11 | F    |  2
> 13 | H    |  9
> 14 | H    |  1
> 15 | I    |  5

Advertisement

Answer